pkg: two postgresql clients

[ml]

Hello,

1.
pkg upgrade show this result:

New packages to be INSTALLED:
postgresql13-client: 13.7
postgresql15-client: 15.b1_1

Installed packages to be UPGRADED:
firefox: 103.0.1,2 -> 103.0.2,2
nspr: 4.34 -> 4.34.1
opencore-amr: 0.1.5 -> 0.1.6
py39-psutil: 5.9.1_1 -> 5.9.1_2

There are two different PG clients. Is this correct?

2.
The command 
pkg install  firefox
New packages to be INSTALLED:
postgresql15-client: 15.b1_1
Installed packages to be UPGRADED:
firefox: 103.0.1,2 -> 103.0.2,2

PG is not a dependent package of firefox?

I use version:
postgres -V 
postgres (PostgreSQL) 14.1

Franz

Re: index row size 2720 exceeds btree version 4

[Albrecht Dreß]

Am 10.08.22 06:57 schrieb(en) Daulat:

We are facing an error while uploading data in a table that has two B-tree
indexes. As per the Postgres documentation there is a limitation of b-tree
index size but I need to know if there is any alternative to overcome this
issue.

[snip]

UNIQUE INDEX uk_gvoa_gi_ad ON test.groupedvuln_asset USING btree (group_id,
hostip, macaddress, fqdn, scanid)"


I had a similar issue, which I solved by creating an index on the hash of the 
concatenation of the items, i.e. something like

CREATE UNIQUE INDEX uk_gvoa_gi_ad ON test.groupedvuln_asset
USING btree (sha256(group_id::bytea || hostip::bytea || macaddress::bytea 
|| fqdn::bytea || scanid::bytea));

Not guaranteed to be 100% collision free, though, but should be sufficient for 
most real-world use cases.

Hth,
Albrecht.


pgp8yl4K3dTtz.pgp
Description: PGP signature

Re: pkg: two postgresql clients

[Peter]

On Wed, Aug 10, 2022 at 09:25:37AM +0200, m...@ft-c.de wrote:

Hi Franz,

 You will get much better targeted help with such questions
at https://forums.freebsd.org (if it is FreeBSD you're running) or
in German on https://www.bsdforen.de/ (for all tastes of Berkekey).

Something is apparently wrong here. postgres-15 is AFAIK the development
branch, and it should not even be possible to install two versions
at the same time on the same node.

Which repository server are You using? (I compile locally, so I don't
know what might be offered from the public servers.) And what is Your
OS version?
 

cheerio,
PMc


! Hello,
! 
! 1.
! pkg upgrade show this result:
! 
! New packages to be INSTALLED:
! postgresql13-client: 13.7
! postgresql15-client: 15.b1_1
! 
! Installed packages to be UPGRADED:
! firefox: 103.0.1,2 -> 103.0.2,2
! nspr: 4.34 -> 4.34.1
! opencore-amr: 0.1.5 -> 0.1.6
! py39-psutil: 5.9.1_1 -> 5.9.1_2
! 
! There are two different PG clients. Is this correct?
! 
! 2.
! The command 
! pkg install  firefox
! New packages to be INSTALLED:
! postgresql15-client: 15.b1_1
! Installed packages to be UPGRADED:
! firefox: 103.0.1,2 -> 103.0.2,2
! 
! PG is not a dependent package of firefox?
! 
! I use version:
! postgres -V 
! postgres (PostgreSQL) 14.1
! 
! Franz

Re: index row size 2720 exceeds btree version 4

[Peter J. Holzer]

On 2022-08-10 10:27:46 +0530, Daulat wrote:
> Error:
> 
> " index row size 2720 exceeds btree version 4 maximum 2704 for index
> ""uk_gvoa_gi_ad"" 54000"
> uk_gvoa_gi_ad
> 
> Index:
> 
> UNIQUE INDEX pk_gvoa_id ON test.groupedvuln_asset USING btree
> (groupedvuln_orphanasset_id)"
> 
> UNIQUE INDEX uk_gvoa_gi_ad ON test.groupedvuln_asset USING btree (group_id,
> hostip, macaddress, fqdn, scanid)"

How are those fields defined? I would expect a group id to be 4 or 8
bytes, a host ip 16 bytes, a mac address 6 bytes and an fqdn at most 255
bytes. So without the scan id we are at 285 bytes. maybe a bit more due
to overhead. That leaves about 2400 bytes for the scan id. I don't know
what a scanid is, but 2000+ bytes for an id seems excessive.

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Surprisingly forgiving behavior when a case expression is terminated with "end case"

[Bryn Llewellyn]

The account of the CASE expression here:

https://www.postgresql.org/docs/current/functions-conditional.html#FUNCTIONS-CASE

says that it's terminated with the keyword END (just as I've always 
understood)—i.e. not with the PL/pgSQL CASE statement's  END CASE.

Moreover CASE is a reserved word—as a "create table case(…)" attempt shows. Yet 
CASE is tolerated (using PG 14.4) here:

select 1 as case;

In fact, any reserved word that I try (like IF, THEN, and so on) is accepted as 
an alias. This seems to me to be wrong. What do you (all) think?

This outcome inspired this test:

create table t(k serial primary key, v text);
insert into t(v) values ('a'), (null);
select
  k,
  case
when v is null then ''
elsev
  end case
from t order by k;

I suppose that this is showing nothing more than what I already did. Everything 
that I've shown so far behaves the same if PG 11.

So then I tried the "typo" in a PL/pgSQL subprogram:

create function f(arr in text[])
  returns text
  language plpgsql
as $body$
declare
  a text;
  r text := '';
begin
  foreach a in array arr loop
a := case
   when a is null then ''
   elsea
 end case;
r := r||a||', ';
  end loop;
  return r;
end;
$body$;

select f(array['a', null::text, 'b']);

The "create function" succeeds. And the "select f()" executes without error to 
produce the result that I expect. In PG 14.4.

But in PG 11.9, the "create function" causes this error:

ERROR:  syntax error at or near "case"
LINE 13:  end case;

It seems, then, that at some version boundary between PG 11 and PG 14, 
forgiveness was introduced in this secnario, too.

Was this change to forgive what seems to be to be a straight syntax error 
deliberate? After all, you (all) thought it to be a syntax error in some 
scenarios in PG 11—but just not so in all scenarios.

Was it that the original sin of forgiveness in some scenarios could not be 
corrected because of the stronger requirement not to break existing code? And 
so this led to a "bug" fix to forgive that sin more uniformly? If so, then I 
suppose that you might say something in the doc. But there is still a wrinkle. 
This:

select
  k,
  case
when v is null then ''
elsev
  end dog
from t order by k;

runs without error. But this (in PG 14.4)

select
  k,
  case
when v is null then ''
elsev
  end case dog
from t order by k;

still fails with a syntax error:

ERROR:  syntax error at or near "dog"
LINE 6:   end case dog

So even in "current", the "end case" type isn't forgiven in all scenarios.

p.s. You can guess that I stumbled on this in the context of a fairly large 
demo app where just one of the subprograms had the "end case" typo that I 
showed above. Nothing prompted me to spot my mistake until I tested my code 
using YugabyteDB. I'm embarrassed to say that our current version still uses 
the PG 11 SQL processing code. But a soon-to-be-published new YB version will 
use PG 13. And "soon" after that, we hope to remain current with the current PG.

Re: Surprisingly forgiving behavior when a case expression is terminated with "end case"

[Tom Lane]

Bryn Llewellyn  writes:
> Moreover CASE is a reserved word—as a "create table case(…)" attempt shows. 
> Yet CASE is tolerated (using PG 14.4) here:

> select 1 as case;

> In fact, any reserved word that I try (like IF, THEN, and so on) is accepted 
> as an alias. This seems to me to be wrong. What do you (all) think?

I think we've spent a great deal of blood, sweat, and tears
making that so, or as nearly so as we could.  We will in
fact take any keyword after "AS", and in recent versions many
non-reserved keywords will work that way without "AS".

(Mind you, I think the SQL spec made a serious design error
in allowing "AS" to be optional.  But we have to live with that
as best we can.)

regards, tom lane

Re: Surprisingly forgiving behavior when a case expression is terminated with "end case"

[Adrian Klaver]

On 8/10/22 11:59, Bryn Llewellyn wrote:

The account of the CASE expression here:

https://www.postgresql.org/docs/current/functions-conditional.html#FUNCTIONS-CASE 



says that it's terminated with the keyword END (just as I've always 
understood)—i.e. not with the PL/pgSQL CASE statement's  END CASE.


Moreover CASE is a reserved word—as a "create table case(…)" attempt 
shows. Yet CASE is tolerated (using PG 14.4) here:


*select 1 as case;
*
In fact, any reserved word that I try (like IF, THEN, and so on) is 
accepted as an alias. This seems to me to be wrong. What do you (all) think?


But documented:

https://www.postgresql.org/docs/current/sql-keywords-appendix.html

" Even reserved key words are not completely reserved in PostgreSQL, but 
can be used as column labels (for example, SELECT 55 AS CHECK, even 
though CHECK is a reserved key word)."



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

Why is DEFAULT much faster than UPDATE?

[André Hänsel]

This question is out of curiosity, just to learn more about the internals of
PostgreSQL.

The goal was to add a not null bool column filled with "false", but with
"true" as the default for new rows.

The naïve approach would be:
ALTER TABLE foo ADD COLUMN slow bool NOT NULL DEFAULT true;
UPDATE foo SET slow = false;

This takes a certain, non-negligible amount of time.

This on the other hand achieves the same result and is almost instant:
ALTER TABLE foo ADD COLUMN fast bool NOT NULL DEFAULT false;
ALTER TABLE foo ALTER COLUMN fast SET DEFAULT true;

Where does the difference come from, how are those handled internally?

Fiddle:
https://dbfiddle.uk/?rdbms=postgres_14&fiddle=56595e8ee397a5bc48b84277da3133
a9

Re: Why is DEFAULT much faster than UPDATE?

[Adrian Klaver]

On 8/10/22 16:02, André Hänsel wrote:

This question is out of curiosity, just to learn more about the internals of
PostgreSQL.

The goal was to add a not null bool column filled with "false", but with
"true" as the default for new rows.

The naïve approach would be:
ALTER TABLE foo ADD COLUMN slow bool NOT NULL DEFAULT true;
UPDATE foo SET slow = false;

This takes a certain, non-negligible amount of time.

This on the other hand achieves the same result and is almost instant:
ALTER TABLE foo ADD COLUMN fast bool NOT NULL DEFAULT false;
ALTER TABLE foo ALTER COLUMN fast SET DEFAULT true;

Where does the difference come from, how are those handled internally?


From here:

https://www.postgresql.org/docs/current/sql-altertable.html

"When a column is added with ADD COLUMN and a non-volatile DEFAULT is 
specified, the default is evaluated at the time of the statement and the 
result stored in the table's metadata. That value will be used for the 
column for all existing rows. If no DEFAULT is specified, NULL is used. 
In neither case is a rewrite of the table required."




Fiddle:
https://dbfiddle.uk/?rdbms=postgres_14&fiddle=56595e8ee397a5bc48b84277da3133
a9






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

Re: Surprisingly forgiving behavior when a case expression is terminated with "end case"

[Bryn Llewellyn]

t...@sss.pgh.pa.us wrote:

> I think we've spent a great deal of blood, sweat, and tears making that so, 
> or as nearly so as we could. We will in fact take any keyword after "AS", and 
> in recent versions many non-reserved keywords will work that way without "AS".
> 
> (Mind you, I think the SQL spec made a serious design error in allowing "AS" 
> to be optional. But we have to live with that as best we can.)

Also, adrian.kla...@aklaver.com wrote:

> Even reserved key words are not completely reserved in PostgreSQL, but can be 
> used as column labels (for example, SELECT 55 AS CHECK, even though CHECK is 
> a reserved key word):
> https://www.postgresql.org/docs/current/sql-keywords-appendix.html

Thank you both. I never would have guessed that a word with "reserved" status 
could be used as a column alias (with or without preceding it with AS). "not 
completely reserved in PostgreSQL" makes this sound like a PG special.

So I was caught out yet again. And I fear that I'll continue to be caught out 
with other things—and maybe this one too, at some later date, when I've 
forgotten the present exchanges…

Anyway, I believe that I have the answer to my question. And my new mental 
model allowed me to predict that, as presented, this would work:

create function f()
  returns text
  language plpgsql
as $body$
declare
  a constant int := 3;
  b constant int := 5;
  c constant int := 7;
begin
  return a + b case /* + c */;
end;
$body$;

select f();

It does! It predicted, too, that when "+ c" is uncommented, "create function" 
would fail with a syntax error. And that prediction also held out.

Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario

[Bryn Llewellyn]

My code example ended up quite big—so I'll show it to you all only if you ask. 
But it's easy to describe. My script does this:

1. It creates three non-superuser roles: "data", "code", and "client".

2. It creates a text-book masters-and-details table pair with owner "data".

Each table has the obvious PK column. The "details" table has the obvious FK 
column. And each table has a payload column "v". The FK constraint is defined 
thus:

  constraint details_fk foreign key(mk)
references masters(mk)
match full
on delete cascade
on update restrict
initially deferred

3. It creates a DELETE trigger at each timing point on each table.

That's eight in all: [before|after] * [row|statement] * [masters|details].

The trigger functions have the same names as the triggers. And each name pair 
reflects the three degrees of freedom—for example "before_statement_masters".

Each trigger function simply does a "raise info" to report its name, the return 
value from "current_role",  (and, in the "row" case, the value of "old.v"). And 
then it does "return null".

The trigger functions are owned by "data" and are explicitly marked "security 
invoker". (The results are the same without this marking—as expected.)

(In my real use case, the trigger functions are marked "security definer". But 
I did this test in order to understand the rules.)

"data" grants appropriate privileges to "code" to let its functions succeed.

4. It creates three "security definer" procedures with owner "code"

"cr_m_and_ds()" inserts specified "details" rows and then their specified 
"masters" row. (That's why the FK constraint is "initially deferred". The 
use-case from which this test is derived needs this.)

"del_m()" deletes a specified "masters" row—which implies the cascade-delete of 
its details.

"del_ds()" deletes all the "details" rows for a specified master.

"code" grants "execute" on these to "client". ("client " owns no objects.)

5. "client" invokes "code.cr_m_and_ds()".

It's called to insert a single "masters" row with "masters.v" set to 'Mary' and 
a single "details" row with "details.v" set to 'shampoo'.

6. "client" invokes "code.del_m()" on 'Mary'.

Here's what I see:

before_statement_masters: [code]
before_row_masters: [code] Mary
before_statement_details: [data]
before_row_details: [data] shampoo
after_row_masters: [code] Mary
after_statement_masters: [code]
after_row_details: [code] shampoo
after_statement_details: [code]

(I stripped the noisy "psql:0.sql:32: INFO:" preamble for each output line by 
hand.)

I was surprised that the value from "current_role" is *not* the table owner, 
"data", in all cases. (This is how triggers behave in Oracle database.) Rather, 
it's mainly (but not always) "code". I could be persuaded that, in the 
cascade-delete case, the invoking role is the owner of the "masters" table 
rather than the role, "code" that performs the "delete" from "masters"—but that 
would maybe be a stretch. Anyway, if this is the intention, why is it like this 
only for the "before" timing points for the triggers on "details"?

7. Starting with the same 'Mary'-'shampoo' pair, client invokes "code.del_ds()" 
on 'Mary'

With the same set-up, and using this instead of "del_m()", this is the result:

before_statement_details: [code]
before_row_details: [code] shampoo
after_row_details: [code] shampoo
after_statement_details: [code]

Here the value for "current_role" from each trigger is the same. These results 
are in line with the common case in the first test.

I read the section "Triggers on Data Changes" 
(https://www.postgresql.org/docs/current/plpgsql-trigger.html#PLPGSQL-DML-TRIGGER
 
).
 But there's no hit on the page for any of "security", "invoker", or "definer". 
And I couldn't find wording without these terms that addresses what I describe 
here.

Re: Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario

[David G. Johnston]

On Wed, Aug 10, 2022 at 6:53 PM Bryn Llewellyn  wrote:

> My code example ended up quite big—so I'll show it to you all only if you
> ask. But it's easy to describe. My script does this:
>

Then maybe you should spend some time making a smaller code example that
still shows the desired behavior but can be easily read and executed by
others.  In particular, your description of simply returning NULL for all
triggers seems suspect.  If only two of the eight triggers show the problem
then the example only needs two triggers to show the presence of the
unexpected current_role and to get clarity why it is that.  All the stuff
that is working as expected is just noise; that is the stuff that can be
summed up with words on a first pass.


>
> I read the section "Triggers on Data Changes" (
> https://www.postgresql.org/docs/current/plpgsql-trigger.html#PLPGSQL-DML-TRIGGER).
> But there's no hit on the page for any of "security", "invoker", or
> "definer". And I couldn't find wording without these terms that addresses
> what I describe here.
>
>
As the behavior you are pointing out has nothing to do with pl/pgsql
specifically, but rather the runtime environment of triggers in the server,
it is not surprising the lack of discussion of this topic in that part of
the documentation.

David J.

Re: Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario

[Bryn Llewellyn]

david.g.johns...@gmail.com wrote:

> …you should spend some time making a smaller code example that still shows 
> the desired behavior but can be easily read and executed by others. In 
> particular, your description of simply returning NULL for all triggers seems 
> suspect. If only two of the eight triggers show the problem then the example 
> only needs two triggers to show the presence of the unexpected current_role 
> and to get clarity why it is that. All the stuff that is working as expected 
> is just noise; that is the stuff that can be summed up with words on a first 
> pass.
>  
> As the behavior you are pointing out has nothing to do with pl/pgsql 
> specifically, but rather the runtime environment of triggers in the server, 
> it is not surprising the lack of discussion of this topic in that part of the 
> documentation.

You said "simply returning NULL for all triggers seems suspect.". Yes, it would 
be! I took my approach from "The usual idiom in DELETE triggers is to return 
OLD." in the "Overview of Trigger Behavior" section. And, indeed, I do say 
"return old" in the code of each of my eight triggers. I'm embarrassed to say 
that I simply did a typo in my email account.

I'll be happy to make a smaller example. It will, however, need to create 
users, a database, schemas, and some number of triggers. Because the triggers, 
their functions, and everything else about them follow a pattern, I can use 
"format()" and dynamic SQL to generate them. I'll still need those three 
"security definer" procedures to make the table changes that I explained. And 
the code to call these procedures to implement the test. So the result won't be 
exactly small. But, while I'm generating the triggers, I may just as well 
generate all eight. After all, how would I know which of the eight to skip 
while I don't know the intended rules for the current_role?

Am I missing something about how this list is meant to work? Email attachments 
don't make it to the archive for posts to this list 
(https://www.postgresql.org/list/pgsql-general/ 
). Is there a reliable place 
where I can post a code .zip so that readers of the list can download it? I 
asked ages ago about a GitHub-style scheme for filing and tracking PG issues. 
but I was told that this email list, and its cousins, is the system that you 
(all) prefer.

Meanwhile, I'd hoped that what I said would prompt a simple statement of what 
rules are intended. You implied that it's elsewhere in the doc than what I 
thought would be the place to look. A cross-reference to that section, from the 
"Triggers on Data Changes" section (and other sections like "CREATE TRIGGER") 
would help.

Could you please point me to where the statement of rules that I'm seeking is 
made? Or, failing that, simply tell me what the intended rules are?

Should I read your "All the stuff that is working as expected is just noise; 
that is the stuff that can be summed up with words on a first pass." to mean 
that the intended role is that the current_role in a "security invoker" trigger 
function is the role that does the trigger-firing DML on the table?

B.t.w., here's what I had tried before sending the email that started this 
thread. (I didn't want to bore you all with this long story.)

I looked in the "CREATE TRIGGER" section. I found this:

https://www.postgresql.org/docs/current/sql-createtrigger.html#SQL-CREATETRIGGER-NOTES
« To create or replace a trigger on a table, the user must have the TRIGGER 
privilege on the table. The user must also have EXECUTE privilege on the 
trigger function. »

I suppose that this covers the case where a pre-existing trigger function has a 
different owner than will the to-be-created trigger that wants to use it.

This rule is certainly in the general space where the answer to my question 
might be found. But this doc extract doesn't answer it explicitly. However, it 
does seem to imply that the invoking role for a trigger function will be the 
owner of the trigger that uses it. And in my example, this was the table owner. 
(In other words, the Oracle Database rule.) But this is not what I see in six 
out of the eight of my tests.

There are no hits on the "CREATE TRIGGER" page for "current_role" or 
"current_user" (with or without the underscore). Neither are there any hits if 
I say "effective user" or "effective role". I don't know what else to try.

I looked in  the "CREATE FUNCTION" section. It does, of course, explain the 
"security invoker" and "security definer" notions. But doesn't say anything 
there to inform my question. (There are only two hits for the word "trigger" on 
the page. And neither is relevant for my question.)

I tried "Overview of Trigger Behavior" 
(https://www.postgresql.org/docs/current/trigger-definition.html 
). But again 
found nothing.

Of course, I tried Google, and the PG doc's own search, for all the w