Not able to purge partition

[veem v]

Hello All,
 We created two partitioned tables one being parent and other child. Both
are range partitioned and one table has a foreign key to the other parent
table. Now , when we run the partition maintenance job to drop the older
partitions which meet the partition retention criteria, it's not letting us
drop any of the partitions from the parent table. Child table works fine
though.

 And when we try using cascade , it drops the foreign key constraint from
all the partitions.  Can you please let me know how to make the partition
maintenance work in such a scenario while maintaining the foreign key
intact, as we are expecting the foreign key to be attached to the
respective partition only but not the full table?

 And this partition maintenance job which runs through cron scheduler in an
automated way is failing on a daily basis for us without being able to drop
any of the historical partitions from the parent partitioned table.

 Below is the test case:-

CREATE TABLE schema1.test_part_drop_parent
(
c1 varchar(36)  NOT NULL ,
c2_part_date timestamp with time zone  NOT NULL ,
c3  numeric(12,0)  NOT NULL ,
CONSTRAINT test_part_drop_parent_PK PRIMARY KEY (c1,c2_part_date)
) PARTITION BY RANGE (c2_part_date);

CREATE INDEX idx1_test_part_parent ON schema1.test_part_drop_parent( C3
ASC);


CREATE TABLE schema1.test_part_drop_child
(
C1_child   varchar(36)  NOT NULL ,
C1   varchar(36)  NOT NULL ,
c2_part_date timestamp with time zone  NOT NULL ,
C3  numeric(12,0)  NOT NULL ,
CONSTRAINT test_part_drop_child_PK PRIMARY KEY (C1_child,c2_part_date),
CONSTRAINT test_part_drop_child_FK1 FOREIGN KEY (C1,c2_part_date)
REFERENCES schema1.test_part_drop_parent(c1,c2_part_date)
) PARTITION BY RANGE (c2_part_date);

CREATE INDEX test_part_drop_child_fk ON schema1.test_part_drop_child( c1,
c2_part_date);

CREATE INDEX test_part_drop_child_idx ON schema1.test_part_drop_child(
c1_child, c2_part_date);


select partman.create_parent(
   p_parent_table := 'schema1.test_part_drop_child',
   p_control := 'c2_part_date',
   p_type := 'native',
   p_interval := '1 day',
   p_premake := 5,
   p_start_partition => '2024-02-01 00:00:00'
);

update partman.part_config set infinite_time_partitions = 'true' ,
retention = '1 months', retention_keep_table='false',
retention_keep_index='false'
where parent_table = 'schema1.test_part_drop_child';


select partman.create_parent(
   p_parent_table := 'schema1.test_part_drop_parent',
   p_control := 'c2_part_date',
   p_type := 'native',
   p_interval := '1 day',
   p_premake := 5,
   p_start_partition => '2024-02-01 00:00:00'
);

update partman.part_config set infinite_time_partitions = 'true' ,
retention = '1 months', retention_keep_table='false',
retention_keep_index='false'
where parent_table = 'schema1.test_part_drop_parent';


select partman.run_maintenance('schema1.test_part_drop_child');

select partman.run_maintenance('schema1.test_part_drop_parent');


SQL Error [P0001]: ERROR: cannot drop table
schema1.test_part_drop_parent_p2024_02_01 because other objects depend on it
CONTEXT: SQL statement "DROP TABLE
schema1.test_part_drop_parent_p2024_02_01"
PL/pgSQL function
drop_partition_time(text,interval,boolean,boolean,text,timestamp with time
zone) line 213 at EXECUTE
PL/pgSQL function partman.run_maintenance(text,boolean,boolean) line 195 at
assignment
DETAIL: constraint test_part_drop_child_fk1 on table
schema1.test_part_drop_child depends on table
schema1.test_part_drop_parent_p2024_02_01
HINT: Use DROP ... CASCADE to drop the dependent objects too.
CONTEXT: PL/pgSQL function
drop_partition_time(text,interval,boolean,boolean,text,timestamp with time
zone) line 308 at RAISE
PL/pgSQL function partman.run_maintenance(text,boolean,boolean) line 195 at
assignment
DETAIL:
HINT:
Where: PL/pgSQL function partman.run_maintenance(text,boolean,boolean) line
413 at RAISE

Re: After 10 -> 15 upgrade getting "cannot commit while a portal is pinned" on one python function

[Adrian Klaver]

On 3/20/24 15:52, Jeff Ross wrote:

On 3/20/24 16:25, Adrian Klaver wrote:


On 3/20/24 15:18, Jeff Ross wrote:

Greetings!

I built a trigger fired process that copies an "order" from our 
production database to our dev database.  An order, in this case, is 
an initial row from a table and all of the rows in all of the tables 
in that database/schema that are needed to satisfy all of the foreign 
key constraints for the original insert. Through a web page, one of 
our folks can select a schema and an order id to copy.  That 
information is then inserted into a table.  A trigger attached to 
that table takes care of copying the necessary rows using a function 
that uses both plython3u and psycopg2.  I can supply the source code 
if that will help.


I think that will help, especially the interaction between psycopg2 
and plpython3u.



As requested:

https://openvistas.net/copy_orders_to_dev.html


1) I have not gone through this thoroughly enough to figure out what is 
going on.


2) Things I have noticed, may not be relevant.

a) from psycopg2 import sql
   Never used.

b) #prod_database_connection.set_session(autocommit=True)
   #dev_database_connection.set_session(autocommit=True)

   Why are they commented out?

c) prod_database_connection_string = "host='pgbouncer' dbname='%s' 
application_name = '%s'"


dev_database_connection_string = "host='pgbouncer' dbname='%s' 
application_name = '%s'"


What version of PgBouncer?

How is it setup?

d) Why predefine all those cursors()?

e) Why is database global?



Jeff





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

Re: After 10 -> 15 upgrade getting "cannot commit while a portal is pinned" on one python function

[Tom Lane]

Adrian Klaver  writes:
> Haven't had a chance to go through this yet. I'm going to say though 
> that Tom Lane is looking for a shorter generic case that anyone could 
> run on their system.

Yeah, it's a long way from that trigger function definition to a
working (i.e. failing) example.  Shortening the trigger might help by
eliminating some parts of the infrastructure that would need to be
shown --- but nobody's going to try to reverse-engineer all that.

regards, tom lane

Re: After 10 -> 15 upgrade getting "cannot commit while a portal is pinned" on one python function

[Adrian Klaver]

On 3/20/24 15:52, Jeff Ross wrote:

On 3/20/24 16:25, Adrian Klaver wrote:


On 3/20/24 15:18, Jeff Ross wrote:

Greetings!

I built a trigger fired process that copies an "order" from our 
production database to our dev database.  An order, in this case, is 
an initial row from a table and all of the rows in all of the tables 
in that database/schema that are needed to satisfy all of the foreign 
key constraints for the original insert. Through a web page, one of 
our folks can select a schema and an order id to copy.  That 
information is then inserted into a table.  A trigger attached to 
that table takes care of copying the necessary rows using a function 
that uses both plython3u and psycopg2.  I can supply the source code 
if that will help.


I think that will help, especially the interaction between psycopg2 
and plpython3u.



As requested:

https://openvistas.net/copy_orders_to_dev.html


Haven't had a chance to go through this yet. I'm going to say though 
that Tom Lane is looking for a shorter generic case that anyone could 
run on their system.




Jeff





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

Re: After 10 -> 15 upgrade getting "cannot commit while a portal is pinned" on one python function

[Jeff Ross]

On 3/20/24 16:50, Tom Lane wrote:

Jeff Ross  writes:

I then get this error:
NOTICE:  update cargotel_common.copy_orders_to_dev set copy_completed =
't', copy_completed_timestamp = clock_timestamp() where id = 21
ERROR:  cannot commit while a portal is pinned

Would you mind supplying a self-contained example that triggers this?

regards, tom lane



I'll see if I can up with something.

Thanks for the reply!

Jeff

Re: After 10 -> 15 upgrade getting "cannot commit while a portal is pinned" on one python function

[Jeff Ross]

On 3/20/24 16:25, Adrian Klaver wrote:


On 3/20/24 15:18, Jeff Ross wrote:

Greetings!

I built a trigger fired process that copies an "order" from our 
production database to our dev database.  An order, in this case, is 
an initial row from a table and all of the rows in all of the tables 
in that database/schema that are needed to satisfy all of the foreign 
key constraints for the original insert. Through a web page, one of 
our folks can select a schema and an order id to copy.  That 
information is then inserted into a table.  A trigger attached to 
that table takes care of copying the necessary rows using a function 
that uses both plython3u and psycopg2.  I can supply the source code 
if that will help.


I think that will help, especially the interaction between psycopg2 
and plpython3u.



As requested:

https://openvistas.net/copy_orders_to_dev.html

Jeff

Re: After 10 -> 15 upgrade getting "cannot commit while a portal is pinned" on one python function

[Tom Lane]

Jeff Ross  writes:
> I then get this error:

> NOTICE:  update cargotel_common.copy_orders_to_dev set copy_completed = 
> 't', copy_completed_timestamp = clock_timestamp() where id = 21
> ERROR:  cannot commit while a portal is pinned

Would you mind supplying a self-contained example that triggers this?

regards, tom lane

Re: After 10 -> 15 upgrade getting "cannot commit while a portal is pinned" on one python function

[Adrian Klaver]

On 3/20/24 15:18, Jeff Ross wrote:

Greetings!

I built a trigger fired process that copies an "order" from our 
production database to our dev database.  An order, in this case, is an 
initial row from a table and all of the rows in all of the tables in 
that database/schema that are needed to satisfy all of the foreign key 
constraints for the original insert.  Through a web page, one of our 
folks can select a schema and an order id to copy.  That information is 
then inserted into a table.  A trigger attached to that table takes care 
of copying the necessary rows using a function that uses both plython3u 
and psycopg2.  I can supply the source code if that will help.


I think that will help, especially the interaction between psycopg2 and 
plpython3u.



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

After 10 -> 15 upgrade getting "cannot commit while a portal is pinned" on one python function

[Jeff Ross]

Greetings!

I built a trigger fired process that copies an "order" from our 
production database to our dev database.  An order, in this case, is an 
initial row from a table and all of the rows in all of the tables in 
that database/schema that are needed to satisfy all of the foreign key 
constraints for the original insert.  Through a web page, one of our 
folks can select a schema and an order id to copy.  That information is 
then inserted into a table.  A trigger attached to that table takes care 
of copying the necessary rows using a function that uses both plython3u 
and psycopg2.  I can supply the source code if that will help.


On postgresql 10 using plpython2, this function worked great.

After migration to 15 (now 15.5) and a switch to plpython3 (no code 
change needed inside the function) logging inside the function tells me 
that everything completes except the exit.


I then get this error:

NOTICE:  update cargotel_common.copy_orders_to_dev set copy_completed = 
't', copy_completed_timestamp = clock_timestamp() where id = 21

ERROR:  cannot commit while a portal is pinned

What the heck?

I did find this error inside the source code.  This is from 15.6 source:

jross@workstation:~/postgresql-15.6$ grep -R -C20 "cannot commit while a 
portal is pinned" *

src/backend/utils/mmgr/portalmem.c- */
src/backend/utils/mmgr/portalmem.c-bool
src/backend/utils/mmgr/portalmem.c-PreCommit_Portals(bool isPrepare)
src/backend/utils/mmgr/portalmem.c-{
src/backend/utils/mmgr/portalmem.c-    bool        result = false;
src/backend/utils/mmgr/portalmem.c-    HASH_SEQ_STATUS status;
src/backend/utils/mmgr/portalmem.c-    PortalHashEnt *hentry;
src/backend/utils/mmgr/portalmem.c-
src/backend/utils/mmgr/portalmem.c- hash_seq_init(, PortalHashTable);
src/backend/utils/mmgr/portalmem.c-
src/backend/utils/mmgr/portalmem.c-    while ((hentry = (PortalHashEnt 
*) hash_seq_search()) != NULL)

src/backend/utils/mmgr/portalmem.c-    {
src/backend/utils/mmgr/portalmem.c-        Portal        portal = 
hentry->portal;

src/backend/utils/mmgr/portalmem.c-
src/backend/utils/mmgr/portalmem.c-        /*
src/backend/utils/mmgr/portalmem.c-         * There should be no pinned 
portals anymore. Complain if someone
src/backend/utils/mmgr/portalmem.c-         * leaked one. Auto-held 
portals are allowed; we assume that whoever

src/backend/utils/mmgr/portalmem.c-         * pinned them is managing them.
src/backend/utils/mmgr/portalmem.c-         */
src/backend/utils/mmgr/portalmem.c-        if (portal->portalPinned && 
!portal->autoHeld)
src/backend/utils/mmgr/portalmem.c:            elog(ERROR, "cannot 
commit while a portal is pinned");

src/backend/utils/mmgr/portalmem.c-
src/backend/utils/mmgr/portalmem.c-        /*
src/backend/utils/mmgr/portalmem.c-         * Do not touch active 
portals --- this can only happen in the case of
src/backend/utils/mmgr/portalmem.c-         * a multi-transaction 
utility command, such as VACUUM, or a commit in

src/backend/utils/mmgr/portalmem.c-         * a procedure.
src/backend/utils/mmgr/portalmem.c-         *
src/backend/utils/mmgr/portalmem.c-         * Note however that any 
resource owner attached to such a portal is
src/backend/utils/mmgr/portalmem.c-         * still going to go away, so 
don't leave a dangling pointer.  Also
src/backend/utils/mmgr/portalmem.c-         * unregister any snapshots 
held by the portal, mainly to avoid
src/backend/utils/mmgr/portalmem.c-         * snapshot leak warnings 
from ResourceOwnerRelease().

src/backend/utils/mmgr/portalmem.c-         */
src/backend/utils/mmgr/portalmem.c-        if (portal->status == 
PORTAL_ACTIVE)

src/backend/utils/mmgr/portalmem.c-        {
src/backend/utils/mmgr/portalmem.c-            if (portal->holdSnapshot)
src/backend/utils/mmgr/portalmem.c-            {
src/backend/utils/mmgr/portalmem.c-                if (portal->resowner)
src/backend/utils/mmgr/portalmem.c- 
UnregisterSnapshotFromOwner(portal->holdSnapshot,

src/backend/utils/mmgr/portalmem.c-                     portal->resowner);
src/backend/utils/mmgr/portalmem.c- portal->holdSnapshot = NULL;
src/backend/utils/mmgr/portalmem.c-            }

Do I have any idea of how to fix this after reading this bit of code?  No.

This error has been reported here before on December 2, 2022 and in 
other places as well.


https://www.postgresql.org/message-id/1061909348.200334.1669970706749%40mail.yahoo.com

No responses though to this message though.

In the code this:

src/backend/utils/mmgr/portalmem.c-     /*
src/backend/utils/mmgr/portalmem.c-         * There should be no pinned 
portals anymore. Complain if someone
src/backend/utils/mmgr/portalmem.c-         * leaked one. Auto-held 
portals are allowed; we assume that whoever

src/backend/utils/mmgr/portalmem.c-         * pinned them is managing them.
src/backend/utils/mmgr/portalmem.c-         */

makes me wonder if this error is specific to plpython3?

I can think of a way to maybe workaround this but this seems to be 

Re: Dropping a temporary view?

[Adrian Klaver]

On 3/20/24 13:00, Celia McInnis wrote:



On Wed, Mar 20, 2024 at 2:15 PM Adrian Klaver > wrote:


__


On 3/20/24 10:54 AM, Celia McInnis wrote:

Comments below more to sort out the process in my head then anything
else.

Hi Adrian

The only behaviour changed for the debugging was to make the view
non-temporary, so that I could verify in psql that the content of
the view was what I wanted it to be. Debugging CGI software can be
quite difficult, so it's always good to have debugging hooks as a
part of the software - I know that I always have a DEBUG flag
which, if on, prints out all kinds of stuff into a debug file, and
I just had my software set a different name for DEBUG mode's
non-temporary view than I was using for the temporary view, as
advised by Christophe Pettus.


This indicates you are working in different sessions and therefore
creating a regular view to see the same data in all sessions.

Previously this regular view was named the same as the temporary
view you create in the production database.

Now you name that regular view a unique name not to conflict with
the temporary view name(s).


No, unfortunately I didn't do an explain on the slow query - and
it's too late now since the views are removed. However, I never
had a delay when waiting for the view to be created in my web
software, so, I'll just proceed being more careful and hope that
the delay seen was due to some big mess I created.


In your original post you say the delay occurred on a SELECT not a
CREATE VIEW after:

Correct. But the initial CREATE VIEW was done  as a SELECT from the 
database, so if the create view was quick, I thought that the select 
from the view would be equally quick. Is this a faulty assumption?



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

"CREATE VIEW defines a view of a query. The view is not physically 
materialized. Instead, the query is run every time the view is 
referenced in a query."


In addition the 'canned' query is running against tables(excepting the 
VALUES case) which in turn maybe getting queries(SELECT, INSERT, UPDATE, 
DELETE) from other sources. This means that each SELECT from a view 
could be seeing an entirely different state.


The above is in reference to a regular(temporary or not) view not a:

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

"CREATE MATERIALIZED VIEW defines a materialized view of a query. The 
query is executed and used to populate the view at the time the command 
is issued (unless WITH NO DATA is used) and may be refreshed later using 
REFRESH MATERIALIZED VIEW."





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

Re: Dropping a temporary view?

[Celia McInnis]

On Wed, Mar 20, 2024 at 2:15 PM Adrian Klaver 
wrote:

>
> On 3/20/24 10:54 AM, Celia McInnis wrote:
>
> Comments below more to sort out the process in my head then anything else.
>
> Hi Adrian
>
> The only behaviour changed for the debugging was to make the view
> non-temporary, so that I could verify in psql that the content of the view
> was what I wanted it to be. Debugging CGI software can be quite difficult,
> so it's always good to have debugging hooks as a part of the software - I
> know that I always have a DEBUG flag which, if on, prints out all kinds of
> stuff into a debug file, and I just had my software set a different name
> for DEBUG mode's non-temporary view than I was using for the temporary
> view, as advised by Christophe Pettus.
>
> This indicates you are working in different sessions and therefore
> creating a regular view to see the same data in all sessions.
>
> Previously this regular view was named the same as the temporary view you
> create in the production database.
>
> Now you name that regular view a unique name not to conflict with the
> temporary view name(s).
>
> No, unfortunately I didn't do an explain on the slow query - and it's too
> late now since the views are removed. However, I never had a delay when
> waiting for the view to be created in my web software, so, I'll just
> proceed being more careful and hope that the delay seen was due to some big
> mess I created.
>
> In your original post you say the delay occurred on a SELECT not a CREATE
> VIEW after:
>
Correct. But the initial CREATE VIEW was done  as a SELECT from the
database, so if the create view was quick, I thought that the select from
the view would be equally quick. Is this a faulty assumption?

> "DROP VIEW tempview;
> DROP VIEW
>
> postgresql did that successfully, but when I then did
>
> select * from tempview:
>
> "
>
> Where the select would have been on the regular view named tempview.
>
>
>
> Thanks,
> Celia McInnis
>
> On Wed, Mar 20, 2024 at 1:01 PM Adrian Klaver 
> wrote:
>
>> On 3/20/24 09:51, Celia McInnis wrote:
>> > The view is being used in some web query software that multiple people
>> > will be accessing and the contents of the view depend on what the
>> person
>> > is querying, so I think that temporary views or tables are a good idea.
>> > I change to non-temporary views or tables (in a test version of the
>> > software which is not web-crawl-able) when I'm trying to debug things,
>> > and I guess I have to be careful to clean those up when I switch back
>> to
>> > the temporary tables/views.
>>
>> Why change behavior for the tests? Seems that sort of negates the value
>> of the testing.
>>
>> Have you run EXPLAIN ANALYZE on the problem query?
>>
>>
>> >
>> >
>> >
>> > On Wed, Mar 20, 2024 at 11:46 AM Adrian Klaver
>> > mailto:adrian.kla...@aklaver.com>> wrote:
>> >
>> > On 3/20/24 08:39, Celia McInnis wrote:
>> >  > Ok, thanks - so I guess that means that if there is both a
>> > temporary and
>> >  > a non temporary view called "tempvie",
>> >  >
>> >  > DROP VIEW tempview;
>> >  >
>> >  > will remove the 1st tempview found, which with my path is the
>> > temporary
>> >  > one. Is there some reason why it then took 7 minutes to select
>> > from the
>> >  > non-temporary view tempview after I dropped the temporary view
>> > tempview?
>> >  >
>> >  > I have sometimes had some very long query times when running
>> query
>> >  > software, and maybe they are resulting from my switching between
>> >  > temporary and non-temporary views of the same name while
>> > debugging. If
>> >  > so, is there something I should be doing to clean up any
>> temporary
>> >  > messes I am creating?
>> >
>> > What is the purpose of the temp view over the the regular view
>> process?
>> >
>> > How do they differ in data?
>> >
>> > Is all the above happening in one session?
>> >
>> > Have you run EXPLAIN ANALYZE on the select from the regular view?
>> >
>> >  >
>> >  > Thanks,
>> >  > Celia McInnis
>> >  >
>> >
>> >
>> > --
>> > Adrian Klaver
>> > adrian.kla...@aklaver.com 
>> >
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
>> --
> Adrian klaveradrian.kla...@aklaver.com
>
>

Re: Dropping a temporary view?

[Adrian Klaver]

On 3/20/24 10:54 AM, Celia McInnis wrote:

Comments below more to sort out the process in my head then anything else.

Hi Adrian

The only behaviour changed for the debugging was to make the view 
non-temporary, so that I could verify in psql that the content of the 
view was what I wanted it to be. Debugging CGI software can be quite 
difficult, so it's always good to have debugging hooks as a part of 
the software - I know that I always have a DEBUG flag which, if on, 
prints out all kinds of stuff into a debug file, and I just had my 
software set a different name for DEBUG mode's non-temporary view than 
I was using for the temporary view, as advised by Christophe Pettus.


This indicates you are working in different sessions and therefore 
creating a regular view to see the same data in all sessions.


Previously this regular view was named the same as the temporary view 
you create in the production database.


Now you name that regular view a unique name not to conflict with the 
temporary view name(s).


No, unfortunately I didn't do an explain on the slow query - and it's 
too late now since the views are removed. However, I never had a delay 
when waiting for the view to be created in my web software, so, I'll 
just proceed being more careful and hope that the delay seen was due 
to some big mess I created.


In your original post you say the delay occurred on a SELECT not a 
CREATE VIEW after:


"DROP VIEW tempview;
DROP VIEW

postgresql did that successfully, but when I then did

select * from tempview:

"

Where the select would have been on the regular view named tempview.




Thanks,
Celia McInnis

On Wed, Mar 20, 2024 at 1:01 PM Adrian Klaver 
 wrote:


On 3/20/24 09:51, Celia McInnis wrote:
> The view is being used in some web query software that multiple
people
> will be accessing and the contents of the view depend on what
the person
> is querying, so I think that temporary views or tables are a
good idea.
> I change to non-temporary views or tables (in a test version of the
> software which is not web-crawl-able) when I'm trying to debug
things,
> and I guess I have to be careful to clean those up when I switch
back to
> the temporary tables/views.

Why change behavior for the tests? Seems that sort of negates the
value
of the testing.

Have you run EXPLAIN ANALYZE on the problem query?


>
>
>
> On Wed, Mar 20, 2024 at 11:46 AM Adrian Klaver
> mailto:adrian.kla...@aklaver.com>>
wrote:
>
>     On 3/20/24 08:39, Celia McInnis wrote:
>      > Ok, thanks - so I guess that means that if there is both a
>     temporary and
>      > a non temporary view called "tempvie",
>      >
>      > DROP VIEW tempview;
>      >
>      > will remove the 1st tempview found, which with my path is the
>     temporary
>      > one. Is there some reason why it then took 7 minutes to
select
>     from the
>      > non-temporary view tempview after I dropped the temporary
view
>     tempview?
>      >
>      > I have sometimes had some very long query times when
running query
>      > software, and maybe they are resulting from my switching
between
>      > temporary and non-temporary views of the same name while
>     debugging. If
>      > so, is there something I should be doing to clean up any
temporary
>      > messes I am creating?
>
>     What is the purpose of the temp view over the the regular
view process?
>
>     How do they differ in data?
>
>     Is all the above happening in one session?
>
>     Have you run EXPLAIN ANALYZE on the select from the regular
view?
>
>      >
>      > Thanks,
>      > Celia McInnis
>      >
>
>
>     --
>     Adrian Klaver
> adrian.kla...@aklaver.com 
>

-- 
Adrian Klaver

adrian.kla...@aklaver.com


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

Re: Dropping a temporary view?

[David G. Johnston]

Top-posting is frowned upon on these lists.  Please try to reply online or
at worse after the comments you are referencing.

On Wed, Mar 20, 2024, 10:54 Celia McInnis  wrote:

>
>
> No, unfortunately I didn't do an explain on the slow query - and it's too
> late now since the views are removed. However, I never had a delay when
> waiting for the view to be created in my web software, so, I'll just
> proceed being more careful and hope that the delay seen was due to some big
> mess I created.
>

If it isn't reproducible it is hard to diagnose. Given the time difference
if it isn't fundamentally a different view then I'd be inclined to suspect
locking issues as a probable factor.

David J.

>

Re: Dropping a temporary view?

[Celia McInnis]

Hi Adrian

The only behaviour changed for the debugging was to make the view
non-temporary, so that I could verify in psql that the content of the view
was what I wanted it to be. Debugging CGI software can be quite difficult,
so it's always good to have debugging hooks as a part of the software - I
know that I always have a DEBUG flag which, if on, prints out all kinds of
stuff into a debug file, and I just had my software set a different name
for DEBUG mode's non-temporary view than I was using for the temporary
view, as advised by Christophe Pettus.

No, unfortunately I didn't do an explain on the slow query - and it's too
late now since the views are removed. However, I never had a delay when
waiting for the view to be created in my web software, so, I'll just
proceed being more careful and hope that the delay seen was due to some big
mess I created.

Thanks,
Celia McInnis

On Wed, Mar 20, 2024 at 1:01 PM Adrian Klaver 
wrote:

> On 3/20/24 09:51, Celia McInnis wrote:
> > The view is being used in some web query software that multiple people
> > will be accessing and the contents of the view depend on what the person
> > is querying, so I think that temporary views or tables are a good idea.
> > I change to non-temporary views or tables (in a test version of the
> > software which is not web-crawl-able) when I'm trying to debug things,
> > and I guess I have to be careful to clean those up when I switch back to
> > the temporary tables/views.
>
> Why change behavior for the tests? Seems that sort of negates the value
> of the testing.
>
> Have you run EXPLAIN ANALYZE on the problem query?
>
>
> >
> >
> >
> > On Wed, Mar 20, 2024 at 11:46 AM Adrian Klaver
> > mailto:adrian.kla...@aklaver.com>> wrote:
> >
> > On 3/20/24 08:39, Celia McInnis wrote:
> >  > Ok, thanks - so I guess that means that if there is both a
> > temporary and
> >  > a non temporary view called "tempvie",
> >  >
> >  > DROP VIEW tempview;
> >  >
> >  > will remove the 1st tempview found, which with my path is the
> > temporary
> >  > one. Is there some reason why it then took 7 minutes to select
> > from the
> >  > non-temporary view tempview after I dropped the temporary view
> > tempview?
> >  >
> >  > I have sometimes had some very long query times when running query
> >  > software, and maybe they are resulting from my switching between
> >  > temporary and non-temporary views of the same name while
> > debugging. If
> >  > so, is there something I should be doing to clean up any temporary
> >  > messes I am creating?
> >
> > What is the purpose of the temp view over the the regular view
> process?
> >
> > How do they differ in data?
> >
> > Is all the above happening in one session?
> >
> > Have you run EXPLAIN ANALYZE on the select from the regular view?
> >
> >  >
> >  > Thanks,
> >  > Celia McInnis
> >  >
> >
> >
> > --
> > Adrian Klaver
> > adrian.kla...@aklaver.com 
> >
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

Re: Dropping a temporary view?

[Celia McInnis]

Good, that's what I'd hope. I'm still not sure why it took more than 7
minutes in psql to select the old non-temporary view contents after
dropping the newer temporary view of the same name. There were no delays in
producing the original non-temporary view. If I can reproduce the problem
in psql, I'll re-ask. Meanwhile I'll also change my software to use
different view names when using non-temporary iviews for debugging.

Thanks,
Celia McInnis

On Wed, Mar 20, 2024 at 12:33 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Wednesday, March 20, 2024, Celia McInnis 
> wrote:
>>
>>
>> Is there some reason why it then took 7 minutes to select from the
>> non-temporary view tempview after I dropped the temporary view tempview?
>>
>>>
>>>
> The fact that you had and then dropped the temporary view has no
> relationship to how some other unrelated view performs.  That the views
> have the same name is just bad naming/design for this very reason; it harms
> understanding.
>
> David J.
>
>

Re: Dropping a temporary view?

[Adrian Klaver]

On 3/20/24 09:51, Celia McInnis wrote:
The view is being used in some web query software that multiple people 
will be accessing and the contents of the view depend on what the person 
is querying, so I think that temporary views or tables are a good idea. 
I change to non-temporary views or tables (in a test version of the 
software which is not web-crawl-able) when I'm trying to debug things, 
and I guess I have to be careful to clean those up when I switch back to 
the temporary tables/views.


Why change behavior for the tests? Seems that sort of negates the value 
of the testing.


Have you run EXPLAIN ANALYZE on the problem query?






On Wed, Mar 20, 2024 at 11:46 AM Adrian Klaver 
mailto:adrian.kla...@aklaver.com>> wrote:


On 3/20/24 08:39, Celia McInnis wrote:
 > Ok, thanks - so I guess that means that if there is both a
temporary and
 > a non temporary view called "tempvie",
 >
 > DROP VIEW tempview;
 >
 > will remove the 1st tempview found, which with my path is the
temporary
 > one. Is there some reason why it then took 7 minutes to select
from the
 > non-temporary view tempview after I dropped the temporary view
tempview?
 >
 > I have sometimes had some very long query times when running query
 > software, and maybe they are resulting from my switching between
 > temporary and non-temporary views of the same name while
debugging. If
 > so, is there something I should be doing to clean up any temporary
 > messes I am creating?

What is the purpose of the temp view over the the regular view process?

How do they differ in data?

Is all the above happening in one session?

Have you run EXPLAIN ANALYZE on the select from the regular view?

 >
 > Thanks,
 > Celia McInnis
 >


-- 
Adrian Klaver

adrian.kla...@aklaver.com 



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

Re: Dropping a temporary view?

[Rob Sargent]

On 3/20/24 10:51, Celia McInnis wrote:
The view is being used in some web query software that multiple people 
will be accessing and the contents of the view depend on what the 
person is querying, so I think that temporary views or tables are a 
good idea. I change to non-temporary views or tables (in a test 
version of the software which is not web-crawl-able) when I'm trying 
to debug things, and I guess I have to be careful to clean those up 
when I switch back to the temporary tables/views.



Are multiple people seeing the same dataset, or is each getting their 
own data albeit of the same structure?  What mechanism populates the 
web-page?  I ask because I think you might not need to make database 
structures at all.

Re: Dropping a temporary view?

[Christophe Pettus]

> On Mar 20, 2024, at 09:51, Celia McInnis  wrote:
> 
> The view is being used in some web query software that multiple people will 
> be accessing and the contents of the view depend on what the person is 
> querying, so I think that temporary views or tables are a good idea.

There's nothing wrong with temporary views or tables, and the use-case you 
describe is a reasonable one.  The issue comes up when they have the same name 
as a permanent view or table.

It's deterministic which one a query will use.  All temporary objects are in 
the pseudo-schema `pg_temp` (it's a "pseudo-schema" because it's an alias to 
the current session's temporary schema, rather than a schema itself).  By 
default, pg_temp is absent from search_path, which is treated as if it were the 
first entry, so temporary tables and views "mask" the permanent ones.  However, 
if that temporary object doesn't happen to exist, or if pg_temp is explicitly 
moved to a different position in the search path, you could have some 
surprising behavior.

Re: Dropping a temporary view?

[Celia McInnis]

The view is being used in some web query software that multiple people will
be accessing and the contents of the view depend on what the person is
querying, so I think that temporary views or tables are a good idea. I
change to non-temporary views or tables (in a test version of the software
which is not web-crawl-able) when I'm trying to debug things, and I guess I
have to be careful to clean those up when I switch back to the temporary
tables/views.



On Wed, Mar 20, 2024 at 11:46 AM Adrian Klaver 
wrote:

> On 3/20/24 08:39, Celia McInnis wrote:
> > Ok, thanks - so I guess that means that if there is both a temporary and
> > a non temporary view called "tempvie",
> >
> > DROP VIEW tempview;
> >
> > will remove the 1st tempview found, which with my path is the temporary
> > one. Is there some reason why it then took 7 minutes to select from the
> > non-temporary view tempview after I dropped the temporary view tempview?
> >
> > I have sometimes had some very long query times when running query
> > software, and maybe they are resulting from my switching between
> > temporary and non-temporary views of the same name while debugging. If
> > so, is there something I should be doing to clean up any temporary
> > messes I am creating?
>
> What is the purpose of the temp view over the the regular view process?
>
> How do they differ in data?
>
> Is all the above happening in one session?
>
> Have you run EXPLAIN ANALYZE on the select from the regular view?
>
> >
> > Thanks,
> > Celia McInnis
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

Re: Dropping a temporary view?

[David G. Johnston]

On Wednesday, March 20, 2024, Celia McInnis  wrote:
>
>
> Is there some reason why it then took 7 minutes to select from the
> non-temporary view tempview after I dropped the temporary view tempview?
>
>>
>>
The fact that you had and then dropped the temporary view has no
relationship to how some other unrelated view performs.  That the views
have the same name is just bad naming/design for this very reason; it harms
understanding.

David J.

Re: Dropping a temporary view?

[Adrian Klaver]

On 3/20/24 08:39, Celia McInnis wrote:
Ok, thanks - so I guess that means that if there is both a temporary and 
a non temporary view called "tempvie",


DROP VIEW tempview;

will remove the 1st tempview found, which with my path is the temporary 
one. Is there some reason why it then took 7 minutes to select from the 
non-temporary view tempview after I dropped the temporary view tempview?


I have sometimes had some very long query times when running query 
software, and maybe they are resulting from my switching between 
temporary and non-temporary views of the same name while debugging. If 
so, is there something I should be doing to clean up any temporary 
messes I am creating?


What is the purpose of the temp view over the the regular view process?

How do they differ in data?

Is all the above happening in one session?

Have you run EXPLAIN ANALYZE on the select from the regular view?



Thanks,
Celia McInnis




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

Re: SSL error on install of PEM during Posgres install

[Adrian Klaver]

On 3/20/24 06:26, mark bradley wrote:
I am getting the following error message during install of PEM while 
installing Posgres.



How can I fix this?



What is your connection string?

In postgresql.conf what is ssl set to per below?

https://www.postgresql.org/docs/current/runtime-config-connection.html#RUNTIME-CONFIG-CONNECTION-SSL

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

Re: Dropping a temporary view?

[Celia McInnis]

Ok, thanks - so I guess that means that if there is both a temporary and a
non temporary view called "tempvie",

DROP VIEW tempview;

will remove the 1st tempview found, which with my path is the temporary
one. Is there some reason why it then took 7 minutes to select from the
non-temporary view tempview after I dropped the temporary view tempview?

I have sometimes had some very long query times when running query
software, and maybe they are resulting from my switching between temporary
and non-temporary views of the same name while debugging. If so, is there
something I should be doing to clean up any temporary messes I am creating?

Thanks,
Celia McInnis

On Wed, Mar 20, 2024 at 11:12 AM Erik Wienhold  wrote:

> On 2024-03-20 15:58 +0100, Celia McInnis wrote:
> > I am using postresql 16, am trying to use temporary views in a piece of
> > software that I am writing, and would like it to be able to drop and
> > recreate temporary views. It seems from the documentation that I can only
> > use "CREATE OR REPLACE TEMPORARY VIEW" if the replacement view has the
> same
> > columns, so Is there a correct way to drop a temporary view?
> >
> > I can create a temporary view, but get a syntax error when I do what I
> > thought would drop it. Here is a simple example of what doesn't work:
> >
> > tt=# create temporary view tempview as select now() as junk;
> > CREATE VIEW
> > tt=# select * from tempview;
> >  junk
> > ---
> >  2024-03-20 14:21:27.441168+00
> > (1 row)
> >
> > tt=# drop temporary view tempview;
> > ERROR:  syntax error at or near "temporary"
> > LINE 1: drop temporary view tempview;
> >  ^
>
> It's just DROP VIEW for normal and temporary views.
>
> > Also, when I then tried (I formerly had a non-temporary view called
> > tempview)
> >
> > DROP VIEW tempview;
> > DROP VIEW
> >
> > postgresql did that successfully, but when I then did
> >
> > select * from tempview:
> >
> > postgresql hung for a long time (more than 7 minutes) before returning
> the
> > contents of some previous view tempview (a previous (temporary, I guess)
> > view by that name that was created by my software when I was not
> creating a
> > temporary view?). I really wasn't expecting this, so if someone can
> > explain, that would be great.
>
> The first view must have been a regular (non-temporary) one.  It is then
> possible to create a temporary view of the same name that shadows the
> original view if pg_temp is searched first, which is the default if you
> haven't modified search_path.  But it's not possible to create a second
> temporary view of the same name because they live in the same namespace
> (pg_temp_N):
>
> regress=# create view tempview as select 1 a;
> CREATE VIEW
> regress=# select * from tempview;
>  a
> ---
>  1
> (1 row)
>
> regress=# create temp view tempview as select 2 b;
> CREATE VIEW
> regress=# select * from tempview;
>  b
> ---
>  2
> (1 row)
>
> regress=# create temp view tempview as select 3 c;
> ERROR:  relation "tempview" already exists
> regress=# select * from tempview;
>  b
> ---
>  2
> (1 row)
>
> regress=# drop view tempview;
> DROP VIEW
> regress=# select * from tempview;
>  a
> ---
>  1
> (1 row)
>
> --
> Erik
>

Re: Dropping a temporary view?

[Erik Wienhold]

On 2024-03-20 15:58 +0100, Celia McInnis wrote:
> I am using postresql 16, am trying to use temporary views in a piece of
> software that I am writing, and would like it to be able to drop and
> recreate temporary views. It seems from the documentation that I can only
> use "CREATE OR REPLACE TEMPORARY VIEW" if the replacement view has the same
> columns, so Is there a correct way to drop a temporary view?
> 
> I can create a temporary view, but get a syntax error when I do what I
> thought would drop it. Here is a simple example of what doesn't work:
> 
> tt=# create temporary view tempview as select now() as junk;
> CREATE VIEW
> tt=# select * from tempview;
>  junk
> ---
>  2024-03-20 14:21:27.441168+00
> (1 row)
> 
> tt=# drop temporary view tempview;
> ERROR:  syntax error at or near "temporary"
> LINE 1: drop temporary view tempview;
>  ^

It's just DROP VIEW for normal and temporary views.

> Also, when I then tried (I formerly had a non-temporary view called
> tempview)
> 
> DROP VIEW tempview;
> DROP VIEW
> 
> postgresql did that successfully, but when I then did
> 
> select * from tempview:
> 
> postgresql hung for a long time (more than 7 minutes) before returning the
> contents of some previous view tempview (a previous (temporary, I guess)
> view by that name that was created by my software when I was not creating a
> temporary view?). I really wasn't expecting this, so if someone can
> explain, that would be great.

The first view must have been a regular (non-temporary) one.  It is then
possible to create a temporary view of the same name that shadows the
original view if pg_temp is searched first, which is the default if you
haven't modified search_path.  But it's not possible to create a second
temporary view of the same name because they live in the same namespace
(pg_temp_N):

regress=# create view tempview as select 1 a;
CREATE VIEW
regress=# select * from tempview;
 a
---
 1
(1 row)

regress=# create temp view tempview as select 2 b;
CREATE VIEW
regress=# select * from tempview;
 b
---
 2
(1 row)

regress=# create temp view tempview as select 3 c;
ERROR:  relation "tempview" already exists
regress=# select * from tempview;
 b
---
 2
(1 row)

regress=# drop view tempview;
DROP VIEW
regress=# select * from tempview;
 a
---
 1
(1 row)

-- 
Erik

Dropping a temporary view?

[Celia McInnis]

Hi

I am using postresql 16, am trying to use temporary views in a piece of
software that I am writing, and would like it to be able to drop and
recreate temporary views. It seems from the documentation that I can only
use "CREATE OR REPLACE TEMPORARY VIEW" if the replacement view has the same
columns, so Is there a correct way to drop a temporary view?

I can create a temporary view, but get a syntax error when I do what I
thought would drop it. Here is a simple example of what doesn't work:

tt=# create temporary view tempview as select now() as junk;
CREATE VIEW
tt=# select * from tempview;
 junk
---
 2024-03-20 14:21:27.441168+00
(1 row)

tt=# drop temporary view tempview;
ERROR:  syntax error at or near "temporary"
LINE 1: drop temporary view tempview;
 ^

Also, when I then tried (I formerly had a non-temporary view called
tempview)

DROP VIEW tempview;
DROP VIEW

postgresql did that successfully, but when I then did

select * from tempview:

postgresql hung for a long time (more than 7 minutes) before returning the
contents of some previous view tempview (a previous (temporary, I guess)
view by that name that was created by my software when I was not creating a
temporary view?). I really wasn't expecting this, so if someone can
explain, that would be great.

Thanks,
Celia McInnis

Re: SSL error on install of PEM during Posgres install

[Erik Wienhold]

Hi Mark,

On 2024-03-20 14:26 +0100, mark bradley wrote:
> I am getting the following error message during install of PEM while
> installing Posgres.
> 
> [cid:da2b6d5e-c3b0-430a-98e0-dac72d0aba23]

Please include error messages as plain text.  For reference, screenshot
says: "psql: error: connection to server at "localhost" (::1), port 5433
failed not support SSL, but SSL was required".

> How can I fix this?

Connect with sslmode=prefer :

https://www.postgresql.org/docs/current/libpq-ssl.html#LIBPQ-SSL-SSLMODE-STATEMENTS

-- 
Erik

SSL error on install of PEM during Posgres install

[mark bradley]

I am getting the following error message during install of PEM while installing 
Posgres.

[cid:da2b6d5e-c3b0-430a-98e0-dac72d0aba23]

How can I fix this?