range of composite types!

[Jian He]

 range of composite types. I found this would be a great idea!!!
Question on stackoverflow

DB Fiddle


 source code regress test

ranges of composite types code part:

 504 --
>  505 -- Ranges of composites
>  506 --
>  507
>  508 create type two_ints as (a int, b int);
>  509 create type two_ints_range as range (subtype = two_ints);
>  510
>  511 -- with force_parallel_mode on, this exercises tqueue.c's range
> remapping
>  512 select *, row_to_json(upper(t)) as u from
>  513   (values (two_ints_range(row(1,2), row(3,4))),
>  514   (two_ints_range(row(5,6), row(7,8 v(t);
>

-- composite type range.
> create type mytype as (t1 int, t2 date);
> -- create type my_interval as (t1 int, t2 interval);
> select (2,'2022-01-02')::mytype ;
> create type mytyperange as range(subtype = mytype);
>

I am thinking construct a composite type range that would be equivalent as:

> select a, b::datefrom generate_series(1,8) a,
> generate_series('2022-01-01'::timestamp,
> '2022-01-31'::timestamp, interval '1 day') b;
>
> for that means the following sql queries should return* false:*

select mytyperange (
> (1,'2022-01-01')::mytype,
> (8, '2022-01-31')::mytype, '[]') @> (2, '2020-01-19')::mytype;
>


>  select
> (2, '2020-01-19')::mytype <@
> mytyperange(
> (1,'2022-01-01')::mytype,
> (8, '2022-01-31')::mytype, '[]') ;
>


> --does the range overlaps, that is, have any common element.
> select
> mytyperange ((2,'2020-12-30')::mytype,
> (2, '2020-12-31')::mytype)
> &&
> mytyperange(
> (1,'2022-01-01')::mytype,
> (8, '2022-01-31')::mytype) ;
>

from the db fiddle link, so far I failed.
If this is possible then we may need a *subtype_diff *function and *canonical
*function.

pg_stat_activity.query empty

[Robert Lichtenberger]

When executing "SELECT * FROM pg_stat_activity" I get lots of rows that 
have an empty "query" column.


According to [1] this column should contain the "Text of this backend's 
most recent query", either a query that is currently running or the last 
query that was executed.


So how comes that a lot of the rows in pg_stat_activity have an empty 
"query"?



https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW

Re: pg_stat_activity.query empty

[David G. Johnston]

On Tue, Apr 26, 2022 at 6:55 AM Robert Lichtenberger <
r.lichtenber...@synedra.com> wrote:

> When executing "SELECT * FROM pg_stat_activity" I get lots of rows that
> have an empty "query" column.
>
> According to [1] this column should contain the "Text of this backend's
> most recent query", either a query that is currently running or the last
> query that was executed.
>
> So how comes that a lot of the rows in pg_stat_activity have an empty
> "query"?
>
>
>
> https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW
>
>
They've never executed a query.  Probably due to connection pooling opening
connections for the pool but never needing to hand them out.

David J.

Re: pg_stat_activity.query empty

[Laurenz Albe]

On Tue, 2022-04-26 at 06:59 -0700, David G. Johnston wrote:
> On Tue, Apr 26, 2022 at 6:55 AM Robert Lichtenberger 
>  wrote:
> > When executing "SELECT * FROM pg_stat_activity" I get lots of rows that 
> > have an empty "query" column.
> > 
> > According to [1] this column should contain the "Text of this backend's 
> > most recent query", either a query that is currently running or the last 
> > query that was executed.
> > 
> > So how comes that a lot of the rows in pg_stat_activity have an empty 
> > "query"?
> > 
> > https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW
> 
> They've never executed a query.  Probably due to connection pooling opening 
> connections for the pool but never needing to hand them out.

An alternative option is that the last query sent was an empty string.

Yours,
Laurenz Albe

Re: pg_stat_activity.query empty

[Ian Lawrence Barwick]

2022年4月26日(火) 23:24 Laurenz Albe :
>
> On Tue, 2022-04-26 at 06:59 -0700, David G. Johnston wrote:
> > On Tue, Apr 26, 2022 at 6:55 AM Robert Lichtenberger 
> >  wrote:
> > > When executing "SELECT * FROM pg_stat_activity" I get lots of rows that
> > > have an empty "query" column.
> > >
> > > According to [1] this column should contain the "Text of this backend's
> > > most recent query", either a query that is currently running or the last
> > > query that was executed.
> > >
> > > So how comes that a lot of the rows in pg_stat_activity have an empty
> > > "query"?
> > >
> > > https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW
> >
> > They've never executed a query.  Probably due to connection pooling opening 
> > connections for the pool but never needing to hand them out.
>
> An alternative option is that the last query sent was an empty string.

Or it's the kind of backend which doesn't normally execute queries.

epp=# SELECT backend_type FROM pg_stat_activity WHERE query = '';
backend_type
--
autovacuum launcher
pglogical supervisor
logical replication launcher
pglogical manager 16389
background writer
checkpointer
walwriter
(7 rows)


Regards

Ian Barwick


-- 
EnterpriseDB: https://www.enterprisedb.com

row level security on conflict do update

[alias]

git.postgresql.org Git - postgresql.git/blob -
src/test/regress/sql/rowsecurity.sql


> 58
> 
> CREATE TABLE category (
> 59
> 
> cidint primary key,
> 60
> 
> cname  text
> 61
> 
> );
> 62
> 
> GRANT ALL ON category TO public;
> 63
> 
> INSERT INTO category VALUES
> 64
> 
> (11, 'novel'),
> 65
> 
> (22, 'science fiction'),
> 66
> 
> (33, 'technology'),
> 67
> 
> (44, 'manga');
> 68
> 
> 69
> 
> CREATE TABLE document (
> 70
> 
> did int primary key,
> 71
> 
> cid int references category(cid),
> 72
> 
> dlevel  int not null,
> 73
> 
> dauthor name,
> 74
> 
> dtitle  text
> 75
> 
> );
> 76
> 
> GRANT ALL ON document TO public;
> 77
> 
> INSERT INTO document VALUES
> 78
> 
> ( 1, 11, 1, 'regress_rls_bob', 'my first novel'),
> 79

Backing up a DB excluding certain tables

[JORGE MALDONADO]

Hi,

I am using *pg_dump* to backup a DB but I would like to exclude some tables
so I added the *-T* switch with a pattern like this: *aspnet*.**. The
backup command  includes the *-s* switch to consider the schema only
because I do not need the data. However, even with the *-T* switch, the
tables with the above pattern are created and I also want to exclude their
creation. Is this possible? Or maybe I am not setting the pattern
correctly. This is the command I am issuing:

pg_dump -E win1252 -f c:/temp/respaldo.backup -F p -n riopoderoso -h
localhost -p 5432 -U postgres -W -T aspnet*.* -s riopoderoso

Respectfully,
Jorge Maldonado

Re: Backing up a DB excluding certain tables

[Adrian Klaver]

On 4/26/22 12:15 PM, JORGE MALDONADO wrote:

Hi,

I am using *pg_dump* to backup a DB but I would like to exclude some 
tables so I added the *-T* switch with a pattern like this: *aspnet*.**. 
The backup command  includes the *-s* switch to consider the schema only 
because I do not need the data. However, even with the *-T* switch, the 
tables with the above pattern are created and I also want to exclude 
their creation. Is this possible? Or maybe I am not setting the pattern 
correctly. This is the command I am issuing:


pg_dump -E win1252 -f c:/temp/respaldo.backup -F p -n riopoderoso -h 
localhost -p 5432 -U postgres -W -T aspnet*.* -s riopoderoso


Try:

-T 'aspnet*.*'

Per:

https://www.postgresql.org/docs/current/app-pgdump.html

"When using wildcards, be careful to quote the pattern if needed to 
prevent the shell from expanding the wildcards; see Examples below."


And Examples is:

https://www.postgresql.org/docs/current/app-pgdump.html#PG-DUMP-EXAMPLES


Respectfully,
Jorge Maldonado



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

How many max_replication_slots?

[andrew cooke]

Hi,

How can I estimate the correct value for max_replication_slots
(logical replication, pg 14)?

As far as I understand, they're needed to keep WAL files on the
publisher when something goes wrong.  But we seem to need way, way
more slots than publishers.  Is it one slot per publisher per table?

We have 14 publishers and seem to need more than 100 slots.  Is this
reasonable, or does it indicate some underlying error?  They all share
the same schema with ~60 tables and the logical replication should
generate the union of the publishers on the subscriber.

Thanks,
Andrew

Re: Backing up a DB excluding certain tables

[Tom Lane]

JORGE MALDONADO  writes:
> I am using *pg_dump* to backup a DB but I would like to exclude some tables
> so I added the *-T* switch with a pattern like this: *aspnet*.**. The
> backup command  includes the *-s* switch to consider the schema only
> because I do not need the data. However, even with the *-T* switch, the
> tables with the above pattern are created and I also want to exclude their
> creation. Is this possible? Or maybe I am not setting the pattern
> correctly. This is the command I am issuing:

> pg_dump -E win1252 -f c:/temp/respaldo.backup -F p -n riopoderoso -h
> localhost -p 5432 -U postgres -W -T aspnet*.* -s riopoderoso

"-n riopoderoso" is already restricting the dump to objects in
schema riopoderoso, so it doesn't look to me like that -T switch
is going to change anything.

I suspect the problem is somewhere else in your process.  Maybe
you are restoring the backup into a database that isn't empty,
but already contains the aspnet*.* tables?

regards, tom lane

Re: row level security on conflict do update

[David G. Johnston]

On Tue, Apr 26, 2022 at 9:44 AM alias  wrote:

>
>> 723
>> 
>> -- Violates USING qual for UPDATE policy p3.
>> 724
>> 
>> --
>> 725
>> 
>> -- UPDATE path is taken, but UPDATE fails purely because *existing* row to be
>> 726
>> 
>> -- updated is not a "novel"/cid 11 (row is not leaked, even though we have
>> 727
>> 
>> -- SELECT privileges sufficient to see the row in this instance):
>> 728
>> 
>> INSERT INTO document VALUES (33, 22, 1, 'regress_rls_bob', 'okay science 
>> fiction'); -- preparation for next statement
>> 729
>> 
>> INSERT INTO document VALUES (33, (SELECT cid from category WHERE cname = 
>> 'novel'), 1, 'regress_rls_bob', 'Some novel, replaces sci-fi') -- takes 
>> UPDATE path
>> 730
>> 
>> ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle;
>> 731
>> 
>> -- Fine (we UPDATE, since INSERT WCOs and UPDATE security barrier quals + 
>> WCOs
>> 732
>> 
>> -- not violated):
>> 733
>> 
>> INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 
>> 'novel'), 1, 'regress_rls_bob', 'my first novel')
>> 734
>> 
>> ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *;
>
>
> POLICY "p3" FOR UPDATE
>> USING ((cid = ( SELECT category.cid
>> FROM category
>> WHERE (category.cname = 'novel'::text
>> WITH CHECK ((dauthor = CURRENT_USER))
>
> row level security p3, if I understand correctly: the *dauthor *shall be
> *currernt_user*
> and updated cid value shall not be *11*
>

The proposed record must have a dauthor value equal to current_user.  That
is the only restriction.

However, the only rows eligible for updating are novels (cid = 11).

With this combination it is possible for an author to update existing
novels authored by someone else to instead be authored by themself.

It is not possible for them to update non-novels authored by themself, or
anyone else.

If it is possible to perform the update the effect of the update must
result in the dauthor value being made equal to current_user or the update
will fail.

Line 729 to 730 fails. That makes sense to me.  but I don't get the line *733
> to 734. *
> Also in the comment section, what does `quals + WCOs` mean?
>
>
WCO := With Check Option clause(the word option is implicit in the actual
clause name)
+ := And
Security Barrier Quals := Using clause

It is basically saying: "both RLS restriction types".

What don't you get about 733/734?

Inserting a row 2 (with valid dauthor value), row 2 exists so move to on
conflict update, row 2 is visible to the update because its cid is 11,
perform update since the new row will have a valid dauthor value.

David J.

Fresh eyeballs needed: input into error

[Rich Shepard]

I'm getting a syntax error with an input into statement and I cannot see the
cause.

A MWE and the result (N.B.: line one wrapped for readability; it ends with
'values'):
insert into people (person_nbr,lname,fname,job_title,company_nbr,loc_nbr,
loc_phone_ext,direct_phone,direct_fax,cell_phone,email,active,comment) values 
(6000,'No','Name',null,404,1,null,null,null,null,null,null,null);


2: ERROR:  syntax error at end of input
LINE 2: (
 ^
What do I keep missing?

TIA,

Rich

Re: Fresh eyeballs needed: input into error

[Adrian Klaver]

On 4/26/22 2:31 PM, Rich Shepard wrote:
I'm getting a syntax error with an input into statement and I cannot see 
the

cause.

A MWE and the result (N.B.: line one wrapped for readability; it ends with
'values'):
insert into people (person_nbr,lname,fname,job_title,company_nbr,loc_nbr,
loc_phone_ext,direct_phone,direct_fax,cell_phone,email,active,comment) 
values (6000,'No','Name',null,404,1,null,null,null,null,null,null,null);


2: ERROR:  syntax error at end of input
LINE 2: (
  ^
What do I keep missing?


I'm guessing some sort of hidden character.

What client are you using to run this?

Where is the query string coming from?



TIA,

Rich





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

Re: Fresh eyeballs needed: input into error

[Rich Shepard]

On Tue, 26 Apr 2022, Adrian Klaver wrote:


I'm guessing some sort of hidden character.


Adrian,

The 'insert into ...' string is a template I've been using for years. It's
worked earlier today, too.


What client are you using to run this?


psql in a linux virtual terminal.


Where is the query string coming from?


I type each row to add to the table by hand.

Rich

Re: Fresh eyeballs needed: input into error

[Rich Shepard]

On Tue, 26 Apr 2022, Adrian Klaver wrote:


I'm guessing some sort of hidden character.


Adrian,

Oh, ... forgot to mention in my response that the MWE values were added to
the template in emacs while I get the same error using psql -d  -f
 in a v.t. or entering 'insert into people values (...)' within
the psql shell.

All throw the same error.

Rich

Re: Fresh eyeballs needed: input into error

[David G. Johnston]

On Tue, Apr 26, 2022 at 2:54 PM Rich Shepard 
wrote:

> On Tue, 26 Apr 2022, Adrian Klaver wrote:
>
> > I'm guessing some sort of hidden character.
>
> Adrian,
>
> Oh, ... forgot to mention in my response that the MWE values were added to
> the template in emacs while I get the same error using psql -d 
> -f
>  in a v.t. or entering 'insert into people values (...)' within
> the psql shell.
>
> All throw the same error.
>
>
What version of PostgreSQL?

Testing on HEAD (and by inspection) nothing you've shown us provokes a
syntax error in PostgreSQL.

regression=> insert into people
(person_nbr,lname,fname,job_title,company_nbr,loc_nbr,
regression(>
loc_phone_ext,direct_phone,direct_fax,cell_phone,email,active,comment)
values
regression->
(6000,'No','Name',null,404,1,null,null,null,null,null,null,null);
ERROR:  relation "people" does not exist
LINE 1: insert into people (person_nbr,lname,fname,job_title,company...

No syntax error, that it didn't find the table is expected.

Copied and pasted right from your email.

David J.

Re: Fresh eyeballs needed: input into error

[Tom Lane]

Rich Shepard  writes:
> I'm getting a syntax error with an input into statement and I cannot see the
> cause.

> A MWE and the result (N.B.: line one wrapped for readability; it ends with
> 'values'):
> insert into people (person_nbr,lname,fname,job_title,company_nbr,loc_nbr,
> loc_phone_ext,direct_phone,direct_fax,cell_phone,email,active,comment) values 
> (6000,'No','Name',null,404,1,null,null,null,null,null,null,null);

> 2: ERROR:  syntax error at end of input
> LINE 2: (
>   ^
> What do I keep missing?

Copying-and-pasting from your mail gets me

ERROR:  relation "people" does not exist
LINE 1: insert into people (person_nbr,lname,fname,job_title,company...
^

so there's nothing wrong with the syntax as-presented.  I agree with
Adrian's guess about invisible characters in your input file;
perhaps a control-D or ASCII NUL would produce that symptom.

regards, tom lane

Re: Fresh eyeballs needed: input into error

[Adrian Klaver]

On 4/26/22 2:53 PM, Rich Shepard wrote:

On Tue, 26 Apr 2022, Adrian Klaver wrote:


I'm guessing some sort of hidden character.


Adrian,

Oh, ... forgot to mention in my response that the MWE values were added to
the template in emacs while I get the same error using psql -d 
 -f

 in a v.t. or entering 'insert into people values (...)' within
the psql shell.

All throw the same error.

I cannot replicate. You are going to have to scan the string on your 
end, I am still guessing an odd character.



Rich





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

Re: Fresh eyeballs needed: input into error

[Rich Shepard]

On Tue, 26 Apr 2022, David G. Johnston wrote:


What version of PostgreSQL?


David,

12.7


Testing on HEAD (and by inspection) nothing you've shown us provokes a
syntax error in PostgreSQL.

regression=> insert into people
(person_nbr,lname,fname,job_title,company_nbr,loc_nbr,
regression(>
loc_phone_ext,direct_phone,direct_fax,cell_phone,email,active,comment)
values
regression->
(6000,'No','Name',null,404,1,null,null,null,null,null,null,null);
ERROR:  relation "people" does not exist
LINE 1: insert into people (person_nbr,lname,fname,job_title,company...

No syntax error, that it didn't find the table is expected.


Well, as I wrote, that syntax has worked for years, including earlier this
morning. Perhaps it will work again tomorrow when I log back in to the
workstation.

Thanks,

Rich

Re: Fresh eyeballs needed: input into error

[Rich Shepard]

On Tue, 26 Apr 2022, Tom Lane wrote:


so there's nothing wrong with the syntax as-presented. I agree with
Adrian's guess about invisible characters in your input file; perhaps a
control-D or ASCII NUL would produce that symptom.


tom,

It's probably a transient glitch that will go away after I log out.

Thanks,

Rich

Re: Fresh eyeballs needed: input into error

[Adrian Klaver]

On 4/26/22 3:08 PM, Rich Shepard wrote:

On Tue, 26 Apr 2022, David G. Johnston wrote:


What version of PostgreSQL?


David,

12.7


Testing on HEAD (and by inspection) nothing you've shown us provokes a
syntax error in PostgreSQL.

regression=> insert into people
(person_nbr,lname,fname,job_title,company_nbr,loc_nbr,
regression(>
loc_phone_ext,direct_phone,direct_fax,cell_phone,email,active,comment)
values
regression->
(6000,'No','Name',null,404,1,null,null,null,null,null,null,null);
ERROR:  relation "people" does not exist
LINE 1: insert into people (person_nbr,lname,fname,job_title,company...

No syntax error, that it didn't find the table is expected.


Well, as I wrote, that syntax has worked for years, including earlier this
morning. Perhaps it will work again tomorrow when I log back in to the
workstation.


It is not the syntax, somewhere since this morning you introduced a 
hidden character into the string.




Thanks,

Rich





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

Re: Backing up a DB excluding certain tables

[Tom Lane]

[ please keep the list cc'd ]

JORGE MALDONADO  writes:
> As for the answer by *Tom Lane*, I am not restoring the DB but only getting
> the backup in plain format. I see that tables that contain "AspNet" in
> their name are part of the resulting dumped file. For example, the
> following is part of the resulting backup plain file:

> CREATE TABLE riopoderoso."AspNetRoleClaims" (
> "Id" integer NOT NULL,
> "RoleId" character varying(450) NOT NULL,
> "ClaimType" text,
> "ClaimValue" text
> );

Ah.  Now that you actually showed us what you're doing, there are
two problems:

1. "aspnet*.*" is trying to match a *schema* name starting with "aspnet",
not a *table* name.  What you want is more like "*.aspnet*", or possibly
"riopoderoso.aspnet*".  (You can't just write "aspnet*", because
riopoderoso isn't going to be in pg_dump's search path, and that pattern
would only match tables in the search path.)

2. You're not accounting for case.  Per the discussion of patterns
in the psql reference manual, to match an upper-case name you'd need
to spell it with the correct casing and then put double quotes around
it.

Actually there's a third problem, which is to get the shell to not strip
the double quotes from the pattern before handing it to pg_dump.

For me, a dump command like

pg_dump -n riopoderoso -T '*."AspNet"*' ...

does what you want.  However, I gather you're doing this on Windows,
and I'm not sure whether shell command quoting rules are the same there.
You might need something weird like backslashing the double quotes.

regards, tom lane

"create function... depends on extension..." not supported. Why?

[Bryn Llewellyn]

Note: I’m asking because the answer to the question “Why isn’t X supported?” is 
always useful. For example, if supporting it would imply a logical 
impossibility that I’m too slow to spot, it helps me when someone explains what 
I failed to realize. Equally, it helps me to know when the answer is “It’s just 
a historical accident. It could have been supported. But, now that it isn’t, it 
doesn’t seem worth the effort to bridge that gap” because this shows me that my 
existing mental model is sound.

The background for my question here is that among the documented changes that 
you can make using “alter function”

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

you can specify all of them at “create time” too (but possibly, like “owner”, 
only implicitly) except for “depends on extension”. When you know in advance 
that you want to set this property, you need two statements:

create function f()
 returns int
 language plpgsql
as $body$
begin
 return 42;
end;
$body$;

alter function f()
depends on extension pgcrypto;

Why is this not supported:

create function f()
 returns int
 language plpgsql
 depends on extension pgcrypto
as $body$
begin
 return 42;
end;
$body$;

Re: "create function... depends on extension..." not supported. Why?

[David G. Johnston]

On Tue, Apr 26, 2022 at 5:22 PM Bryn Llewellyn  wrote:

> Note: I’m asking because the answer to the question “Why isn’t X
> supported?” is always useful. For example, if supporting it would imply a
> logical impossibility that I’m too slow to spot, it helps me when someone
> explains what I failed to realize. Equally, it helps me to know when the
> answer is “It’s just a historical accident. It could have been supported.
> But, now that it isn’t, it doesn’t seem worth the effort to bridge that
> gap” because this shows me that my existing mental model is sound.
>

Here is the commit that brought the feature into existence (it includes a
link to the archives for discussion from which you can read or infer
things).

https://github.com/postgres/postgres/commit/f2fcad27d59c8e5c48f8fa0a96c8355e40f24273

David J.

Re: Fresh eyeballs needed: input into error [FIXED]

[Rich Shepard]

On Tue, 26 Apr 2022, Rich Shepard wrote:


It's probably a transient glitch that will go away after I log out.


Yep. I logged out and back in just now. The insert script worked as it
always had before and both rows were added to the table.

I've seen these sort of hang-ups before and didn't want to log out, back
in, and recreate the working environment. Had I done so I'd have saved a lot
of time.

Thanks for your inputs, everyone,

Rich

Re: "create function... depends on extension..." not supported. Why?

[Bryn Llewellyn]

> david.g.johns...@gmail.com wrote:
> 
>> b...@yugabyte.com wrote:
>> 
>> Note: I’m asking because the answer to the question “Why isn’t X supported?” 
>> is always useful. For example, if supporting it would imply a logical 
>> impossibility that I’m too slow to spot, it helps me when someone explains 
>> what I failed to realize. Equally, it helps me to know when the answer is 
>> “It’s just a historical accident. It could have been supported. But, now 
>> that it isn’t, it doesn’t seem worth the effort to bridge that gap” because 
>> this shows me that my existing mental model is sound.
> 
> Here is the commit that brought the feature into existence (it includes a 
> link to the archives for discussion from which you can read or infer things).
> 
> https://github.com/postgres/postgres/commit/f2fcad27d59c8e5c48f8fa0a96c8355e40f24273

The discussion has diverging threads and very many turns. I think that I 
managed to skim through the entire tree. As I read it, the discussion was 
entirely about the semantics of the proposed dependency of a function (or 
procedure) upon an extension. The idea to establish such a dependency using 
“alter function” came up quite early in the discussion. It seems that 
establishing it at “create function” time was never considered.

Unless anybody corrects me, I’ll conclude that it’s perfectly feasible to 
establish the dependency at “create function” time. This would meet an obvious 
niceness goal (symmetry and guessability). It would also increase the 
possibility for component rule re-use in the presentation of the syntax rules. 
But I don’t s’pose that the effort of changing anything here would be 
cost-effective.

Re: "create function... depends on extension..." not supported. Why?

[Tom Lane]

Bryn Llewellyn  writes:
> The discussion has diverging threads and very many turns. I think that I 
> managed to skim through the entire tree. As I read it, the discussion was 
> entirely about the semantics of the proposed dependency of a function (or 
> procedure) upon an extension. The idea to establish such a dependency using 
> “alter function” came up quite early in the discussion. It seems that 
> establishing it at “create function” time was never considered.

Probably not.  Just for the record, it's not true that CREATE and ALTER
always have the same set of options.  An obvious counterexample is that
you can't set the owner to someone different from yourself during CREATE.
There may be others.

I suppose that "DEPENDS ON EXTENSION" was modeled after the commands
to control extension membership, which likewise exist only in ALTER
form because CREATE's behavior for that is hard-wired.  If you wanted
to hand-wave a lot, you could maybe claim that ownership and extension
membership/dependency are similar kinds of relationships and so it
makes sense that the command structures for manipulating them are
similar.  But TBH that would probably be reverse-engineering an
explanation.  I think that "we didn't bother" is more nearly the
situation.

regards, tom lane

Re: Fresh eyeballs needed: input into error [FIXED]

[Bruce Momjian]

On Tue, Apr 26, 2022 at 06:09:42PM -0700, Rich Shepard wrote:
> On Tue, 26 Apr 2022, Rich Shepard wrote:
> 
> > It's probably a transient glitch that will go away after I log out.
> 
> Yep. I logged out and back in just now. The insert script worked as it
> always had before and both rows were added to the table.
> 
> I've seen these sort of hang-ups before and didn't want to log out, back
> in, and recreate the working environment. Had I done so I'd have saved a lot
> of time.
> 
> Thanks for your inputs, everyone,

I am curious what OS psql was using that was fixed by a re-login?

-- 
  Bruce Momjian  https://momjian.us
  EDB  https://enterprisedb.com

  Indecision is a decision.  Inaction is an action.  Mark Batterson

Re: "create function... depends on extension..." not supported. Why?

[Bryn Llewellyn]

> t...@sss.pgh.pa.us wrote:
> 
>> b...@yugabyte.com wrote:
>> 
>> The discussion has diverging threads and very many turns. I think that I 
>> managed to skim through the entire tree. As I read it, the discussion was 
>> entirely about the semantics of the proposed dependency of a function (or 
>> procedure) upon an extension. The idea to establish such a dependency using 
>> “alter function” came up quite early in the discussion. It seems that 
>> establishing it at “create function” time was never considered.
> 
> Probably not.  Just for the record, it's not true that CREATE and ALTER 
> always have the same set of options.  An obvious counterexample is that you 
> can't set the owner to someone different from yourself during CREATE. There 
> may be others.
> 
> I suppose that "DEPENDS ON EXTENSION" was modeled after the commands to 
> control extension membership, which likewise exist only in ALTER form because 
> CREATE's behavior for that is hard-wired.  If you wanted to hand-wave a lot, 
> you could maybe claim that ownership and extension membership/dependency are 
> similar kinds of relationships and so it makes sense that the command 
> structures for manipulating them are similar.  But TBH that would probably be 
> reverse-engineering an explanation.  I think that "we didn't bother" is more 
> nearly the situation.

Thanks, Tom. Just as I’d hoped, I found your “we didn’t bother” reply very 
helpful. I take your point about the inevitability of some differences between 
what “create function” and “alter function” can express.

B.t.w., in Oracle Database, you can create a schema object with any owner as 
long as you have the object-type-specific “ANY” privilege. (You need an 
ordinary object-type-specific privilege just to create objects that you own 
yourself.) The PG model is as different from the Oracle model as it could be in 
the general area of creating, altering, and dropping schema objects. I see that 
“alter” to change the owner only after the fact, requiring as it does a 
superuser, is [almost] a forced choice in PG.

It would seem, though, that syntax could be invented to allow a superuser to 
create an object of any type with any owner. But I s’pose that the usability 
benefit that this would bring would be marginal and it might even tempt bad 
practices.

Re: Fresh eyeballs needed: input into error [FIXED]

[Adrian Klaver]

On 4/26/22 20:11, Bruce Momjian wrote:

On Tue, Apr 26, 2022 at 06:09:42PM -0700, Rich Shepard wrote:

On Tue, 26 Apr 2022, Rich Shepard wrote:




I am curious what OS psql was using that was fixed by a re-login?



Rich uses Slackware.

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

Re: range of composite types!

[David G. Johnston]

On Tuesday, April 26, 2022, Jian He  wrote:

>
> -- composite type range.
>> create type mytype as (t1 int, t2 date);
>> -- create type my_interval as (t1 int, t2 interval);
>> select (2,'2022-01-02')::mytype ;
>> create type mytyperange as range(subtype = mytype);
>>
>
> I am thinking construct a composite type range that would be equivalent
> as:
>
>> select a, b::datefrom generate_series(1,8) a,
>> generate_series('2022-01-01'::timestamp,
>> '2022-01-31'::timestamp, interval '1 day') b;
>>
>> Ranges have to be ordered.  How do you propose to order the above?
Composite type comparisons have defined ordering semantics.  Your results
demonstrate what those are (namely, subsequent fields are used only to
break ties).  If you want different behavior you will have to code it
yourself - possibly including ignoring the generic composite type
infrastructure and make a formal base type of whatever it is you need.

David J.