Thread: Weird problem in 8.0.0

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Hi,

I've just upgraded two servers to 8.0.0, using PGDG RPMs.

One of them is working well; however I'm experiencing problems in the
other one.

I first thought that this was a VACUMM issue; but then I saw that I can't
execute any command.

First case: Connected to template1 using psql and ran VACUMM. Here is the
log output (debug 5) (same thing happens when I run vacuumdb from the
command line)

=============================================================================
<[unknown][unknown]%t>LOG:  connection received: host=[local] port=
<%t>DEBUG:  forked new backend, pid=3104 socket=7
<postgrestemplate1%tauthentication>DEBUG:  received password packet
<postgrestemplate1%tauthentication>LOG:  connection authorized:
user=postgres database=template1
<postgrestemplate1%tauthentication>DEBUG:  postmaster child[3104]:
starting with (
<postgrestemplate1%tauthentication>DEBUG:       postgres
<postgrestemplate1%tauthentication>DEBUG:       -v196608
<postgrestemplate1%tauthentication>DEBUG:       -p
<postgrestemplate1%tauthentication>DEBUG:       template1
<postgrestemplate1%tauthentication>DEBUG:  )
<postgrestemplate1%tstartup>DEBUG:  InitPostgres
<postgrestemplate1%tstartup>DEBUG:  StartTransaction
<postgrestemplate1%tstartup>DEBUG:  name: unnamed; blockState:
DEFAULT; state: INPROGR, xid/subid/cid: 4341/1/0, nestlvl: 1, children: <>
<postgrestemplate1%tstartup>DEBUG:  CommitTransaction
<postgrestemplate1%tstartup>DEBUG:  name: unnamed; blockState:
STARTED; state: INPROGR, xid/subid/cid: 4341/1/0, nestlvl: 1, children: <>
<postgrestemplate1%tidle>DEBUG:  StartTransactionCommand
<postgrestemplate1%tidle>STATEMENT:  VACUUM ;
<postgrestemplate1%tidle>DEBUG:  StartTransaction
<postgrestemplate1%tidle>STATEMENT:  VACUUM ;
<postgrestemplate1%tidle>DEBUG:  name: unnamed; blockState:       DEFAULT;
state: INPROGR, xid/subid/cid: 4342/1/0, nestlvl: 1, children: <>
<postgrestemplate1%tidle>STATEMENT:  VACUUM ;
<postgrestemplate1%tidle>LOG:  statement: VACUUM ;
<postgrestemplate1%tidle>STATEMENT:  VACUUM ;
<postgrestemplate1%tVACUUM>DEBUG:  ProcessUtility
<postgrestemplate1%tVACUUM>STATEMENT:  VACUUM ;
<postgrestemplate1%tVACUUM>DEBUG:  CommitTransaction
<postgrestemplate1%tVACUUM>STATEMENT:  VACUUM ;
<postgrestemplate1%tVACUUM>DEBUG:  name: unnamed; blockState:
STARTED; state: INPROGR, xid/subid/cid: 4342/1/0, nestlvl: 1, children: <>
<postgrestemplate1%tVACUUM>STATEMENT:  VACUUM ;
<postgrestemplate1%tVACUUM>DEBUG:  StartTransaction
<postgrestemplate1%tVACUUM>STATEMENT:  VACUUM ;
<postgrestemplate1%tVACUUM>DEBUG:  name: unnamed; blockState:
DEFAULT; state: INPROGR, xid/subid/cid: 4343/1/0, nestlvl: 1, children: <>
<postgrestemplate1%tVACUUM>STATEMENT:  VACUUM ;
<postgrestemplate1%tVACUUM>DEBUG:  vacuuming
"information_schema.sql_packages"
<postgrestemplate1%tVACUUM>STATEMENT:  VACUUM ;
<postgrestemplate1%tVACUUM>ERROR:  canceling query due to user request
<postgrestemplate1%tVACUUM>STATEMENT:  VACUUM ;
=============================================================================

Now, second case:

template1=# \c proftpd
You are now connected to database "proftpd".
proftpd=# SELECT passwd FROM users WHERE userid='istanbul';
ERROR:  canceling query due to user request

And the log shows:

=============================================================================
<postgresproftpd%tidle>DEBUG:  StartTransactionCommand
<postgresproftpd%tidle>STATEMENT:  SELECT passwd FROM users WHERE
userid='istanbul';
<postgresproftpd%tidle>DEBUG:  StartTransaction
<postgresproftpd%tidle>STATEMENT:  SELECT passwd FROM users WHERE
userid='istanbul';
<postgresproftpd%tidle>DEBUG:  name: unnamed; blockState:       DEFAULT;
state: INPROGR, xid/subid/cid: 4347/1/0, nestlvl: 1, children: <>
<postgresproftpd%tidle>STATEMENT:  SELECT passwd FROM users WHERE
userid='istanbul';
<postgresproftpd%tidle>LOG:  statement: SELECT passwd FROM users WHERE
userid='istanbul';
<postgresproftpd%tidle>STATEMENT:  SELECT passwd FROM users WHERE
userid='istanbul';
<postgresproftpd%tSELECT>ERROR:  canceling query due to user request
<postgresproftpd%tSELECT>STATEMENT:  SELECT passwd FROM users WHERE
userid='istanbul';
<postgresproftpd%tidle>DEBUG:  proc_exit(0)
<postgresproftpd%tidle>DEBUG:  shmem_exit(0)
=============================================================================

What might be the problem? The upgrade process was done as usual...

Regards,

- --
Devrim GUNDUZ
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.tdmsoft.com                         http://www.gunduz.org
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQFB9JaLtl86P3SPfQ4RAgwaAKCfupu287wiCGZntn6+WZmyg8QSBACgp8VO
i/6uRBjyMDdPcFIXCrlP2E8=
=dSc+
-----END PGP SIGNATURE-----

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Hi,

On Mon, 24 Jan 2005, Devrim GUNDUZ wrote:

> I've just upgraded two servers to 8.0.0, using PGDG RPMs.
>
> One of them is working well; however I'm experiencing problems in the
> other one.
<snip>

(scratching head)

I've changed statement_timeout value from 5 to 0; restarted PostgreSQL and
all commands are running now... So, what could be the problem with
statement_timeout? (This was one of three values that I've changed in
postgresql.conf... That's why I resetted it at the first step)

Regards,
- --
Devrim GUNDUZ
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.tdmsoft.com                         http://www.gunduz.org
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQFB9JlStl86P3SPfQ4RAp/mAJ9gFwWWOS2XdViVA2gOMN5bWKvY3ACeKrRi
Tn6ko64ZODPAaYVceO1iq9Q=
=qzv0
-----END PGP SIGNATURE-----

Hello,

I have a function, which stores some Data. Sometimes this function is
called within a transaction an sometimes not. But I need to set a
savepoint in this function. But this is my problem, when I'm not in a
transaction I get this error:

ERROR:  SAVEPOINT may only be used in transaction blocks

How can I check, if there is an transaction?

Thanks for your help,
Stefan Sturm

Devrim GUNDUZ <devrim@gunduz.org> writes:
> I've changed statement_timeout value from 5 to 0; restarted PostgreSQL and
> all commands are running now... So, what could be the problem with
> statement_timeout?

Apparently you need a timeout longer than 5 milliseconds.

Note that the first few queries executed by a backend will take longer
than "usual" because of the overhead of populating the catalog caches.

            regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Hi,

On Mon, 24 Jan 2005, Tom Lane wrote:

>> I've changed statement_timeout value from 5 to 0; restarted PostgreSQL and
>> all commands are running now... So, what could be the problem with
>> statement_timeout?
>
> Apparently you need a timeout longer than 5 milliseconds.
>
> Note that the first few queries executed by a backend will take longer
> than "usual" because of the overhead of populating the catalog caches.

What I'm confused was "miliseconds", I saw it as "seconds" :)

Anyway, thanks.

- --
Devrim GUNDUZ
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.tdmsoft.com                         http://www.gunduz.org
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQFB9SYgtl86P3SPfQ4RAiAIAJ9aXeD9lTZKQKpjKdtvmp+Mczi16ACeNbYW
jtBVhUuI18xw8pF7RGKR1sE=
=iuHJ
-----END PGP SIGNATURE-----