[BUGS] Unlisten / listen in a transaction failure

[Greg Sabino Mullane]

I came across some unusual behavior with listen. Basically, if you 
unlisten and listen inside of a transaction, new notices are not 
picked up right away - but they will show up if you send yourself 
a notice. It also works as expected if you unlisten, commit, and 
then re-listen. Tested on 9.1 and 9.2. Demo psql script:

listen abc; \t
\! psql -p 5491 -c 'notify abc'
select * from pg_listening_channels();

begin; unlisten *; listen abc; commit;
\! psql -p 5491 -c 'notify abc'
select * from pg_listening_channels();

notify abc;

Output of above on 9.1 with psql -e:

listen abc;
LISTEN
Showing only tuples.
NOTIFY
select * from pg_listening_channels();
 abc

Asynchronous notification "abc" received from server process with PID 10879.

begin;
BEGIN
unlisten *;
UNLISTEN
listen abc;
LISTEN
commit;
COMMIT
NOTIFY
select * from pg_listening_channels();
 abc

notify abc;
NOTIFY
Asynchronous notification "abc" received from server process with PID 10882.
Asynchronous notification "abc" received from server process with PID 10876.



-- 
Greg Sabino Mullane g...@endpoint.com
End Point Corporation
PGP Key: 0x14964AC8


signature.asc
Description: Digital signature

Re: [BUGS] Unlisten / listen in a transaction failure

[Tom Lane]

Greg Sabino Mullane  writes:
> I came across some unusual behavior with listen. Basically, if you 
> unlisten and listen inside of a transaction, new notices are not 
> picked up right away - but they will show up if you send yourself 
> a notice. It also works as expected if you unlisten, commit, and 
> then re-listen. Tested on 9.1 and 9.2. Demo psql script:

Huh.  If you run this in an assert-enabled build, it gets an assert
failure:

regression=# listen abc;
LISTEN
regression=# notify abc;
NOTIFY
Asynchronous notification "abc" received from server process with PID 19048.
regression=# begin; unlisten *; listen abc; commit;
BEGIN
UNLISTEN
LISTEN
COMMIT
regression=# notify abc;
The connection to the server was lost. Attempting reset: Failed.

The Assert is

TRAP: FailedAssertion("!(MyProcPid == 
(asyncQueueControl->backend[MyBackendId].pid))", File: "async.c", Line: 1821)

which shows that we aren't actually registered in the global array of
listeners, even though we think we should be.

I think the problem is that we do Exec_ListenPreCommit, then
Exec_UnlistenAllCommit, then Exec_ListenCommit --- and the second
of these undoes our registration as a listener.  That needs rethinking.
Looking at it, the backendHasExecutedInitialListen flag seems pretty
badly thought out too.  It looks to me like we'd be better off with a
flag defined like "amRegisteredListener" that tracks whether we're
currently in the array or not, and during AtCommit_Notify() we shouldn't
deregister as a listener until we've scanned all the pending actions and
know whether we are ending in a no-listens state or not.

regards, tom lane


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

[BUGS] BUG #7873: pg_restore --clean tries to drop tables that don't exist

[autarch]

The following bug has been logged on the website:

Bug reference:  7873
Logged by:  Dave Rolsky
Email address:  auta...@urth.org
PostgreSQL version: 9.2.3
Operating system:   Linux
Description:

When you pass the --clean option to pg_restore it tries to drop tables
without checking if they exist. This results in lots of error output. If
you're running pg_restore via an automated process it's very hard to
distinguish between these "ok" errors and real errors.

It should be using "DROP TABLE IF EXISTS" and the equivalent for
constraints.



-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

[BUGS] BUG #7874: GUC's not in database dumps

[joe]

The following bug has been logged on the website:

Bug reference:  7874
Logged by:  Joe Van Dyk
Email address:  j...@tanga.com
PostgreSQL version: 9.2.1
Operating system:   OSX, Linux
Description:

If I run:
   alter database foo set my.name = 'joe';

that GUC isn't available inside backups of that database.

It does show up when doing "pg_dumpall -s". 

It does *not* show up when doing "pg_dumpall -g"


$ createdb foo
$ psql foo
psql (9.2.1)

foo=# alter database foo set my.name = 'joe';
ALTER DATABASE

foo=# \q

$ pg_dump foo > foo.sql

$ cat foo.sql
--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;

--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: 
--

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;

--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: 
--

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

--
-- Name: public; Type: ACL; Schema: -; Owner: joe
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM joe;
GRANT ALL ON SCHEMA public TO joe;
GRANT ALL ON SCHEMA public TO PUBLIC;

--
-- PostgreSQL database dump complete
--



-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs