RE: How to keep format of views source code as entered?

[Markhof, Ingolf]

Thanks for your comments and thoughts.

I am really surprised that PostgreSQL is unable to keep the source text of a 
view. Honestly, for me the looks like an implementation gap. Consider software 
development. You are writing code in C++ maybe on a UNIX host. And whenever you 
feed you source code into the compiler, it will delete it, keeping the 
resulting executable, only. And you could not even store your source code on 
the UNIX system. Instead, you'd be forced to do so in a separate system, like 
GitHub. Stupid, isn't it? Right. There are good reasons to store the source 
code on GitHub or alike anyhow. Especially when working on larger project and 
when collaborating with many people. But in case of rather small project with a 
few people only, this might be an overkill.

It shouldn't be rocket science to enable PostgreSQL to store the original 
source code as well. It's weird PostgreSQL is not doing it.

Regards,
Ingolf



Verizon Deutschland GmbH - Sebrathweg 20, 44149 Dortmund, Germany - Amtsgericht 
Dortmund, HRB 14952 - Geschäftsführer: Detlef Eppig - Vorsitzender des 
Aufsichtsrats: Francesco de Maio

RE: How to keep format of views source code as entered?

[Andreas Joseph Krogh]

På fredag 08. januar 2021 kl. 09:38:29, skrev Markhof, Ingolf <
ingolf.mark...@de.verizon.com >: 




Thanks for your comments and thoughts.



I am really surprised that PostgreSQL is unable to keep the source text of a 
view. Honestly, for me the looks like an implementation gap. Consider software 
development. You are writing code in C++ maybe on a UNIX host. And whenever you 
feed you source code into the compiler, it will delete it, keeping the 
resulting executable, only. And you could not even store your source code on 
the UNIX system. Instead, you'd be forced to do so in a separate system, like 
GitHub. Stupid, isn't it? Right. There are good reasons to store the source 
code on GitHub or alike anyhow. Especially when working on larger project and 
when collaborating with many people. But in case of rather small project with a 
few people only, this might be an overkill.



It shouldn't be rocket science to enable PostgreSQL to store the original 
source code as well. It's weird PostgreSQL is not doing it.

It isn't rocket-science, of couse, but I'm pretty sure it is implemented like 
this on purpose. PG doesn't store queries you feed it either, nor any other
command. It stores the resulting structure. SQL-scripts, containing DDL/DML 
should be versioned using scm like Git, not rely on the DB to store it. 




-- 
Andreas Joseph Krogh 

Re: How to keep format of views source code as entered?

[Karsten Hilbert]

Am Fri, Jan 08, 2021 at 08:38:29AM + schrieb Markhof, Ingolf:

> I am really surprised that PostgreSQL is unable to keep the
> source text of a view. Honestly, for me the looks like an
> implementation gap. Consider software development. You are
> writing code in C++ maybe on a UNIX host. And whenever you
> feed you source code into the compiler, it will delete it,
> keeping the resulting executable, only.

You expect the compiler to keep your source code for you ?

Most certainly, PostgreSQL does not delete your view source
code, just as the compiler does.

I am not so sure that analogy holds up.

Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B

Suggestion: provide a "TRUNCATE PARTITION" command

[Thomas Kellerer]

Hello,

I wonder if it made sense to add a "TRUNCATE PARTITION" command to Postgres?

Especially during bulk loads it's more efficient to TRUNCATE a partition if I 
know I want to replace all rows, rather than doing a DELETE.

Currently this requires dynamic SQL which isn't always feasible (and might get 
complicated quickly).

So I was thinking that a new command to allow truncating partitions by identifying the 
partitions by "value" rather by name might be helpful in that case.

Something along the lines of:

truncate partitions of base_table
for values in (...);

If the IN part allowed for sub-queries then this could be used to gather the 
partition keys from e.g. a staging table.
In my naive understanding, I would think the current partition pruning code 
(e.g. that picks the partitions when running DELETE) could be used to identify 
the target partitions and then this list would be used to TRUNCATE all 
resulting partitions.

What does the community think about this?

Thomas

Re: How to keep format of views source code as entered?

[Tim . Colles]

On Fri, 8 Jan 2021, Karsten Hilbert wrote:


Am Fri, Jan 08, 2021 at 08:38:29AM + schrieb Markhof, Ingolf:


I am really surprised that PostgreSQL is unable to keep the
source text of a view. Honestly, for me the looks like an
implementation gap. Consider software development. You are
writing code in C++ maybe on a UNIX host. And whenever you
feed you source code into the compiler, it will delete it,
keeping the resulting executable, only.


You expect the compiler to keep your source code for you ?

Most certainly, PostgreSQL does not delete your view source
code, just as the compiler does.

I am not so sure that analogy holds up.



The SQL-92 standard requires the source text of a view to be held in
order to provide the "information_schema" "view definition" column which
"contains a representation of the view descriptors” - to me though it is
open to interpretation what that actually means. MariaDB, like
PostgreSQL, does not not store an exact copy of the view source either.

The SQL-92 standard is completely explicit about column expansion: “NOTE
13: Any implicit column references that were contained in the  associated with the  are replaced by
explicit column references in VIEW_DEFINITION.” - so any view definition
that is stored, solely for the purposes of standard compliance, will at
a minimum have to differ from the original source if the source had any
implicit column references (and by association table references as well
I assume).

Arguably if PostgreSQL held an exact copy of the view definition (except
for alterations under Note 13 above) then it should also store exact
copies of other pre-parsed objects, such as DEFAULT on table columns and
WITH on trigger clauses, in order to be useful under the OP's context.

See also:

http://www.postgresql-archive.org/idea-storing-view-source-in-system-catalogs-td1987401.html
http://www.postgresql-archive.org/Preserving-the-source-code-of-views-td5775163.html
--
The University of Edinburgh is a charitable body, registered in
Scotland, with registration number SC005336.

RE: Autovacuum not functioning for large tables but it is working for few other small tables.

[M Tarkeshwar Rao]

Hi all,

As we know, the VACUUM VERBOSE output has a lot of dependencies from production 
end and is indefinite as of now. We don’t have any clue till now on why exactly 
the auto-vacuum is not working for the table. So we need to have a work around 
to move ahead for the time being.
 
Can you please suggest any workaround so that we can resolve the issue or any 
other way by which we can avoid this situation?

Regards
Tarkeshwar

-Original Message-
From: Tomas Vondra 
Sent: Thursday, December 17, 2020 7:16 AM
To: M Tarkeshwar Rao ; 
pgsql-performa...@postgresql.org
Cc: Neeraj Gupta G ; Atul Parashar 
; Shishir Singh ; 
Ankit Sharma 
Subject: Re: Autovacuum not functioning for large tables but it is working for 
few other small tables.

On 12/16/20 12:55 PM, M Tarkeshwar Rao wrote:
> Hi all,
> 
> We have facing some discrepancy in Postgresql database related to the 
> autovacuum functionality.
> 
> By default autovacuum was enable on Postgres which is used to remove 
> the dead tuples from the database.
> 
> We have observed autovaccum cleaning dead rows from *table_A* but same 
> was not functioning correctly for *table_B* which have a large
> size(100+GB) in comparision to table_A.
> 
> All the threshold level requirements for autovacuum was meet and there 
> are about Million’s of  dead tuples but autovacuum was unable to clear 
> them, which cause performance issue on production server.
> 
> Is autovacuum not working against large sized tables or Is there any 
> parameters which  need to set to make autovacuum functioning?
> 

No, autovacuum should work for tables with any size. The most likely 
explanation is that the rows in the large table were deleted more recently and 
there is a long-running transaction blocking the cleanup. 
Or maybe not, hard to say with the info you provided.

A couple suggestions:

1) enable logging for autovacuum by setting

log_autovacuum_min_duration = 10ms (or similar low value)

2) check that the autovacuum is actually executed on the large table (there's 
last_autovacuum in pg_stat_all_tables)

3) try running VACUUM VERBOSE on the large table, it may tell you that the rows 
can't be cleaned up yet.


regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Re: Suggestion: provide a "TRUNCATE PARTITION" command

[legrand legrand]

Hello,

maybe a naïve plpgsql as proposed in
https://www.postgresql-archive.org/Partitionning-support-for-Truncate-Table-WHERE-td5933642.html
may be an answer

Regards
PAscal



--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Suggestion: provide a "TRUNCATE PARTITION" command

[Thomas Kellerer]

legrand legrand schrieb am 08.01.2021 um 14:57:> maybe a naïve plpgsql as 
proposed in

https://www.postgresql-archive.org/Partitionning-support-for-Truncate-Table-WHERE-td5933642.html
may be an answer


Yes I am aware of that (and that's what I have used so far) - I just thought it 
would make life easier if it didn't require dynamic SQL

Thomas

Missing declaration of _PG_init()

[Jack Orenstein]

I am writing an extension. The docs describe a _PG_init function that will
be called upon
loading the shared library (https://www.postgresql.org/docs/12/xfunc-c.html).
I include
postgres.h and fmgr.h, but on compilation, _PG_init has not been declared.
Grepping the postgres source, _PG_init appears to be involved in
programming language extensions, and the function is declared in plpgsql.h.
Looking at various contrib modules, I see
explicit declarations of _PG_init(void).

Should _PG_init(void) be declared in someplace included by postgres.h or
fmgr.h?

Jack Orenstein

Re: Suggestion: provide a "TRUNCATE PARTITION" command

[Michael Lewis]

On Fri, Jan 8, 2021 at 2:36 AM Thomas Kellerer  wrote:

> Hello,
>
> I wonder if it made sense to add a "TRUNCATE PARTITION" command to
> Postgres?
>
> Especially during bulk loads it's more efficient to TRUNCATE a partition
> if I know I want to replace all rows, rather than doing a DELETE.
>
> Currently this requires dynamic SQL which isn't always feasible (and might
> get complicated quickly).
>
> So I was thinking that a new command to allow truncating partitions by
> identifying the partitions by "value" rather by name might be helpful in
> that case.
>
> Something along the lines of:
>
>  truncate partitions of base_table
>  for values in (...);
>
> If the IN part allowed for sub-queries then this could be used to gather
> the partition keys from e.g. a staging table.
>

For me, it seems too easily error prone such that a single typo in the IN
clause may result in an entire partition being removed that wasn't supposed
to be targeted. Given the user still needs to manually generate that list
somehow, I don't see it as a huge effort to query the partitions and run
individual commands to truncate or detach several partitions manually.

Unless it is in the SQL standard, or allows users to do something that
cannot be easily done otherwise, I see it as a bell / whistle that would
unnecessarily complicate the code. Writing a function that finds the
partition table names and gives back a string with the text of the DDL
commands that needs to be run is simple, and would encourage the user to
review which tables are targeted for truncate command.

Re: Missing declaration of _PG_init()

[Laurenz Albe]

On Fri, 2021-01-08 at 09:49 -0500, Jack Orenstein wrote:
> I am writing an extension. The docs describe a _PG_init function that will be 
> called upon
> loading the shared library (https://www.postgresql.org/docs/12/xfunc-c.html). 
> I include
> postgres.h and fmgr.h, but on compilation, _PG_init has not been declared.
>  Grepping the postgres source, _PG_init appears to be involved in programming 
> language
>  extensions, and the function is declared in plpgsql.h. Looking at various 
> contrib modules, I see
> explicit declarations of _PG_init(void). 
> 
> Should _PG_init(void) be declared in someplace included by postgres.h or 
> fmgr.h?

It is provided by your module, so it must be declared by your module:

extern void _PG_init(void);

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: How to keep format of views source code as entered?

[Tom Lane]

"Markhof, Ingolf"  writes:
> I am really surprised that PostgreSQL is unable to keep the source text
> of a view. Honestly, for me the looks like an implementation gap.

Perhaps, but the "gap" is wider than you seem to think.  Consider

CREATE TABLE t1 (f1 int, f2 text);
CREATE VIEW v1 AS SELECT f2 FROM t1;
ALTER TABLE t1 RENAME COLUMN f2 TO zed;
\d+ v1
View "public.v1"
 Column | Type | Collation | Nullable | Default | Storage  | Description 
+--+---+--+-+--+-
 f2 | text |   |  | | extended | 
View definition:
 SELECT t1.zed AS f2
   FROM t1;

At this point the original text of the view is useless; with
another rename or two it could become downright misleading.

Another issue revolves around the fact that a textual SQL statement
is seldom totally unambiguous.  In the above example, the fact that
"t1" refers to public.t1 and not some other t1 depends on the
search_path as it stood at CREATE VIEW time.  If you change your
search_path you might need an explicit schema qualification.
The reverse-parsed view display accounts for that:

# set search_path = pg_catalog;
# \d+ public.v1
View "public.v1"
 Column | Type | Collation | Nullable | Default | Storage  | Description 
+--+---+--+-+--+-
 f2 | text |   |  | | extended | 
View definition:
 SELECT t1.zed AS f2
   FROM public.t1;

but a static source text could not.  In PG this hazard applies to
functions and operators not only tables.  If pg_dump regurgitated
the original view text, there would be trivially-exploitable
security holes that allow some other user to take control of your
view after a dump/reload.

We actually used to store both text and parsed versions of some
sub-constructs, such as CHECK constraints and column default values.
We got rid of the text versions because there was no reasonable way
to keep them up-to-date.  (And, AFAIR, there hasn't been a lot of
push-back about those catalog columns disappearing.)  So I don't
think we'd accept a patch to store the text form of a view, unless
some solution to these issues were provided.

regards, tom lane

Re: Missing declaration of _PG_init()

[Tom Lane]

Jack Orenstein  writes:
> Should _PG_init(void) be declared in someplace included by postgres.h or
> fmgr.h?

No, because it's something a given module might or might not provide.
Also, a global extern might give the impression that this was a global
function that the core code provides, rather than a per-module function.

regards, tom lane

Re: How to keep format of views source code as entered?

[Adrian Klaver]

On 1/8/21 12:38 AM, Markhof, Ingolf wrote:

Thanks for your comments and thoughts.

I am really surprised that PostgreSQL is unable to keep the source text 
of a view. Honestly, for me the looks like an implementation gap. 
Consider software development. You are writing code in C++ maybe on a 
UNIX host. And whenever you feed you source code into the compiler, it 
will delete it, keeping the resulting executable, only. And you could 
not even store your source code on the UNIX system. Instead, you'd be 
forced to do so in a separate system, like GitHub. Stupid, isn't it? 
Right. There are good reasons to store the source code on GitHub or 
alike anyhow. Especially when working on larger project and when 
collaborating with many people. But in case of rather small project with 
a few people only, this might be an overkill.


The projects I work on are my own personal ones and I find an 
independent version control solution the way to go for the following 
reasons:


1) It is easy.
a) mkdir project_src
b) cd project_src
c) git init
Now you are set.

2) Even my simple projects generally have multiple layers.
a) Database
b) Middleware
c) UI
And also multiple languages. It makes sense to me to keep all
that information in one repo then having each layer operate independently.

3) It allows me to work on test and production code without stepping on 
each other.


4) It serves as an aid to memory. Answers the question; What was I 
thinking when I did that? More important it helps anyone else that might 
have to deal with the code.


FYI, the program I use to manage database changes is 
Sqitch(https://sqitch.org/).




It shouldn't be rocket science to enable PostgreSQL to store the 
original source code as well. It's weird PostgreSQL is not doing it.


Regards,

Ingolf


*Verizon Deutschland GmbH* - Sebrathweg 20, 44149 Dortmund, Germany - 
Amtsgericht Dortmund, HRB 14952 - Geschäftsführer: Detlef Eppig - 
Vorsitzender des Aufsichtsrats: Francesco de Maio





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Suggestion: provide a "TRUNCATE PARTITION" command

[Thomas Kellerer]

Michael Lewis schrieb am 08.01.2021 um 16:32:

On Fri, Jan 8, 2021 at 2:36 AM Thomas Kellerer mailto:sham...@gmx.net>> wrote:

Hello,

I wonder if it made sense to add a "TRUNCATE PARTITION" command to Postgres?

Especially during bulk loads it's more efficient to TRUNCATE a partition if 
I know I want to replace all rows, rather than doing a DELETE.

Currently this requires dynamic SQL which isn't always feasible (and might 
get complicated quickly).

So I was thinking that a new command to allow truncating partitions by identifying 
the partitions by "value" rather by name might be helpful in that case.

Something along the lines of:

      truncate partitions of base_table
      for values in (...);

If the IN part allowed for sub-queries then this could be used to gather 
the partition keys from e.g. a staging table.


For me, it seems too easily error prone such that a single typo in
the IN clause may result in an entire partition being removed that
wasn't supposed to be targeted.


I don't see how this is more dangerous then:

delete from base_table
where partition_key in (...);

which would serve the same purpose, albeit less efficient.


Given the user still needs to
manually generate that list somehow, I don't see it as a huge effort
to query the partitions and run individual commands to truncate or
detach several partitions manually.


Well, the list could come from e.g. a staging table, e.g. "for values IN (select 
some_column from staging_table)"

Re: Suggestion: provide a "TRUNCATE PARTITION" command

[Michael Lewis]

On Fri, Jan 8, 2021 at 9:38 AM Thomas Kellerer  wrote:

> Michael Lewis schrieb am 08.01.2021 um 16:32:
> > On Fri, Jan 8, 2021 at 2:36 AM Thomas Kellerer  sham...@gmx.net>> wrote:
> >
> > Hello,
> >
> > I wonder if it made sense to add a "TRUNCATE PARTITION" command to
> Postgres?
> >
> > Especially during bulk loads it's more efficient to TRUNCATE a
> partition if I know I want to replace all rows, rather than doing a DELETE.
> >
> > Currently this requires dynamic SQL which isn't always feasible (and
> might get complicated quickly).
> >
> > So I was thinking that a new command to allow truncating partitions
> by identifying the partitions by "value" rather by name might be helpful in
> that case.
> >
> > Something along the lines of:
> >
> >   truncate partitions of base_table
> >   for values in (...);
> >
> > If the IN part allowed for sub-queries then this could be used to
> gather the partition keys from e.g. a staging table.
> >
> >
> > For me, it seems too easily error prone such that a single typo in
> > the IN clause may result in an entire partition being removed that
> > wasn't supposed to be targeted.
>
> I don't see how this is more dangerous then:
>
>  delete from base_table
>  where partition_key in (...);
>
> which would serve the same purpose, albeit less efficient.
>

Delete has a rollback option, and you can dry-run to see impacted rows
effectively. Truncate does not.

With delete, you are being more explicit about which rows match and need
removal. By looking at the command, you know exactly what is expected to
happen. With the request to find partitions based on values, you may be
impacting MUCH more data than you meant to. If you think you have monthly
range partitions and actually have year partitions, the truncate could be a
disaster with removing more data than you intended.

It just seems like a foot gun to me, and not one that is particularly
needed since the same result can be achieved easily in two steps. One to
generate to explicit commands that will be run, and one to run them.

Re: Suggestion: provide a "TRUNCATE PARTITION" command

[Thomas Kellerer]

Michael Lewis schrieb am 08.01.2021 um 17:47:

 > For me, it seems too easily error prone such that a single typo in
 > the IN clause may result in an entire partition being removed that
 > wasn't supposed to be targeted.

I don't see how this is more dangerous then:

      delete from base_table
      where partition_key in (...);

which would serve the same purpose, albeit less efficient.


Delete has a rollback option, and you can dry-run to see impacted rows 
effectively. Truncate does not.


TRUNCATE can be rolled back as well.

Re: Suggestion: provide a "TRUNCATE PARTITION" command

[legrand legrand]

What is interesting here with the TRUNCATE WHERE (and in the proposed
plpgsql) is to offer the end user a way to perform a transparent truncate or
delete totally independent of the partitioning scheme (if any, or even if it
has changed).



--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Suggestion: provide a "TRUNCATE PARTITION" command

[Michael Lewis]

On Fri, Jan 8, 2021 at 10:12 AM Thomas Kellerer  wrote:

> Michael Lewis schrieb am 08.01.2021 um 17:47:
> >  > For me, it seems too easily error prone such that a single typo in
> >  > the IN clause may result in an entire partition being removed that
> >  > wasn't supposed to be targeted.
> >
> > I don't see how this is more dangerous then:
> >
> >   delete from base_table
> >   where partition_key in (...);
> >
> > which would serve the same purpose, albeit less efficient.
> >
> >
> > Delete has a rollback option, and you can dry-run to see impacted rows
> effectively. Truncate does not.
>
> TRUNCATE can be rolled back as well.
>

My apologies. There are other concerns with concurrent transactions, but
you are correct that it can be rolled back.

Still, no feedback on the effect that a truncate call is having on the DB
and may be doing more than intended fairly easily. I am not in the hackers
group so I couldn't say this feature would not be implemented. It just
seems unlikely given the philosophies of that group.

Finding memory corruption in an extension

[Jack Orenstein]

An extension I'm creating is causing Postgres to crash, almost certainly
due to memory corruption.  I am using palloc0/pfree, calling SET_VARSIZE,
and generally following the procedures documented here:
https://www.postgresql.org/docs/12/xfunc-c.html. I am also testing my code
outside of Postgres (using alloc/free instead of palloc0/pfree), and
valgrind is not finding any corruption or leaks.

The crash is not completely reproducible, but when it does happen, it's
pretty fast -- create a table, insert a couple of rows, explain a query.
(My goal is to create a GIN index on my datatype, but this crash occurs
without the index.)

I'm interested in advice on how to go about hunting down my problem.
Something along the lines of a debugging malloc, or valgrind, for Postgres.

Jack Orenstein

Re: Finding memory corruption in an extension

[Pavel Stehule]

Hi

pá 8. 1. 2021 v 18:48 odesílatel Jack Orenstein  napsal:

> An extension I'm creating is causing Postgres to crash, almost certainly
> due to memory corruption.  I am using palloc0/pfree, calling SET_VARSIZE,
> and generally following the procedures documented here:
> https://www.postgresql.org/docs/12/xfunc-c.html. I am also testing my
> code outside of Postgres (using alloc/free instead of palloc0/pfree), and
> valgrind is not finding any corruption or leaks.
>
> The crash is not completely reproducible, but when it does happen, it's
> pretty fast -- create a table, insert a couple of rows, explain a query.
> (My goal is to create a GIN index on my datatype, but this crash occurs
> without the index.)
>
> I'm interested in advice on how to go about hunting down my problem.
> Something along the lines of a debugging malloc, or valgrind, for Postgres.
>

The basic feature is using postgres compiled with --enable-cassert flag. It
does lot of checks of memory corruptions

Regards

Pavel




> Jack Orenstein
>

Re: Finding memory corruption in an extension

[Tom Lane]

Pavel Stehule  writes:
> pá 8. 1. 2021 v 18:48 odesílatel Jack Orenstein  napsal:
>> I'm interested in advice on how to go about hunting down my problem.
>> Something along the lines of a debugging malloc, or valgrind, for Postgres.

> The basic feature is using postgres compiled with --enable-cassert flag. It
> does lot of checks of memory corruptions

Yeah, you should absolutely use --enable-cassert when working on C code.

If you need valgrind, there's advice about how to run it at

https://wiki.postgresql.org/wiki/Valgrind

regards, tom lane

Re: How to keep format of views source code as entered?

[raf]

On Fri, Jan 08, 2021 at 08:38:29AM +, "Markhof, Ingolf" 
 wrote:

> Thanks for your comments and thoughts.
> [...]
> And you could not even store your source code on the UNIX
> system. Instead, you'd be forced to do so in a separate system, like
> GitHub. Stupid, isn't it? Right.
> [...] 
> Regards,
> Ingolf

I don't think your conclusions are correct. There is
nothing that can stop you from "storing your source
code on the UNIX system". You don't have to use github.
But even if you do you github, you would first need to
store your source code on a file system, so that a
local git repository could see it, and push it to
github. You don't even have to use git if you really
don't want to.

cheers,
raf

Re: Using more than one LDAP?

[Stephen Frost]

Greetings,

* Paul Förster (paul.foers...@gmail.com) wrote:
> Ok, since LDAP doesn't work that way, I either need to build GSSAPI packages 
> and have the AD admins to provide me with the keytab file or make the 
> transition a "hard" one, i.e. no transition phase. Though I'd rather have 
> liked to see a transition phase where either account could have been used I 
> personally can live with that. It's the developers who will have to change 
> quickly, not me. ;-)

Done correctly, the developers will hopefully be going from "this stupid
thing prompts me to provide a username/password in order to log in" to
"no more prompt for logging in, it just *works*".  Further, as Magnus
explained, you could actually have the mapping to allow user X to log in
by providing GSSAPI credentials Y, if they are actually still going to
be including some username in their connection request to PG (even
though they shouldn't need to, since it'll be the same between their
local Windows/AD login and the GSSAPI user that PG will see).  You
should be able to make both work concurrently thanks to pg_ident.conf.

Thanks,

Stephen


signature.asc
Description: PGP signature

Re: How to keep format of views source code as entered?

[Tim Cross]

Adrian Klaver  writes:

> On 1/8/21 12:38 AM, Markhof, Ingolf wrote:
>> Thanks for your comments and thoughts.
>>
>> I am really surprised that PostgreSQL is unable to keep the source text
>> of a view. Honestly, for me the looks like an implementation gap.
>> Consider software development. You are writing code in C++ maybe on a
>> UNIX host. And whenever you feed you source code into the compiler, it
>> will delete it, keeping the resulting executable, only. And you could
>> not even store your source code on the UNIX system. Instead, you'd be
>> forced to do so in a separate system, like GitHub. Stupid, isn't it?
>> Right. There are good reasons to store the source code on GitHub or
>> alike anyhow. Especially when working on larger project and when
>> collaborating with many people. But in case of rather small project with
>> a few people only, this might be an overkill.
>
> The projects I work on are my own personal ones and I find an
> independent version control solution the way to go for the following
> reasons:
>
> 1) It is easy.
>   a) mkdir project_src
>   b) cd project_src
>   c) git init
> Now you are set.
>
> 2) Even my simple projects generally have multiple layers.
>   a) Database
>   b) Middleware
>   c) UI
> And also multiple languages. It makes sense to me to keep all
> that information in one repo then having each layer operate independently.
>
> 3) It allows me to work on test and production code without stepping on
> each other.
>
> 4) It serves as an aid to memory. Answers the question; What was I
> thinking when I did that? More important it helps anyone else that might
> have to deal with the code.
>
> FYI, the program I use to manage database changes is
> Sqitch(https://sqitch.org/).
>
>>

This is essentially my workflow as well. I have even used sqitch too.

While this has worked well for my projects, attempts to introduce the
discipline necessary to use such a workflow in a team has largely
failed. This seems to be due to 2 main reasons -

1. Lack of SCCM support built into common tools. There are very few
tools which have version control support built in (I believe the jet
brains product does). In particular, pgAdmin would benefit here (maybe
pgadmin4 does, I've not tried it in a few years).

2. Poor SCCM and DBMS Understanding. Despite it being 2021 and both
version control and databases being two very common technologies you
need to interact with as a developer, I'm still surprised at how poorly
many developers understand these tools. I still frequently come across
really bad workflows and practices with version control and code which
uses the database as little more than a bit bucket, which re-implement
searching and sorting at the client level (and then often moan about
poor performance issues).

My editor has good support for psql and psql has always been my goto
tool for PG. As my editor also has good git support, my workflow works
well. However, most people I've worked with prefer things like pgadmin.
Tom Lane responded in this thread to point out some of the complexities
which make it difficult to maintain current code source within the
database itself. This is definitely something which should be kept in
version control. The problem is, if your tool does not support the
version control system, it is too easy to forget/bypass that stage. When
you use something like pgadmin, it is far too easy to modify the source
definitions in the database without ever updating the sources on disk in
the version control working directory and the changes get lost.

The other big challenge is dependency management. Keeping track of what
is affected by a change to a table definition can be a challenge within
a complex system. I've yet to find a good solution to that issue. It is
probably something which needs to be built into a tool. In the past,
I've used a modified sqitch approach that also maintains a small 'dbadm'
schema containing metadata to track dependencies. Although this worked
OK, especially if you understood how all the bits fit together, it still
had many corner cases and to some extent highlighted the complexities involved.

--
Tim Cross

Re: Using more than one LDAP?

[Paul Förster]

Hi Stephen,

> On 08. Jan, 2021, at 22:59, Stephen Frost  wrote:
> 
> Done correctly, the developers will hopefully be going from "this stupid
> thing prompts me to provide a username/password in order to log in" to
> "no more prompt for logging in, it just *works*".  Further, as Magnus
> explained, you could actually have the mapping to allow user X to log in
> by providing GSSAPI credentials Y, if they are actually still going to
> be including some username in their connection request to PG (even
> though they shouldn't need to, since it'll be the same between their
> local Windows/AD login and the GSSAPI user that PG will see).  You
> should be able to make both work concurrently thanks to pg_ident.conf.

I agree. But the company policy is to have users being asked each time they 
want to login somewhere, no matter where. We need to use an RSA tamagotchi at 
least twice to even get somewhere close to being able to launch a tool like 
DbVisualizer or SQL Developer. If we want a shell on a server, we need to use 
the tamagotchi even one more time.

And then, for such tools, or in fact anything, "no more prompt" unfortunately 
is just no option. Some call that security, I call that paranoia. This is why I 
don't care whether GSSAPI is more secure than LDAPS. The whole environment is 
stuffed inside some network zone which is stuffed into another network zone, 
then divided into again some other network zones inside, etc. LDAP and AD are 
in separate zones than the databases, developer's and admin's machines are 
again in some other network zone. Even some databases have their own network 
zones. You get the picture... The best thing is: they still call this single 
sign on because you get to use the same username everywhere. rotfl

From the network perspective, Magnus is right. We have a hacky environment. But 
architecture is not something I have an influence on.

Cheers,
Paul