[BUGS] Error connecting
So it just suddenly stopped working after 1 week for the 2nd time straight. The first time i reinstalled everything, lost database but it worked, now it just doesnt connect. What should i do? I use windows vista, run as administrator and everything
Re: [BUGS] libpq does not manage SSL callbacks properly when other libraries are involved.
Alvaro Herrera wrote: > PoolSnoopy wrote: > >> ***PUSH*** >> >> this bug is really some annoyance if you use automatic build environments. >> I'm using phpunit to run tests and as soon as postgres is involved the php >> cli environment segfaults at the end. this can be worked around by disabling >> ssl but it would be great if the underlying bug got fixed. >> > > This is PHP's bug, isn't it? Why are you complaining here No, this is a problem with the callback/exit functions used by PostgreSQL. We setup callback functions when we use SSL, if somebody else uses SSL we can create a problem. I thought my original report was detailed enough to explain where the problem is coming from. Excerpt from original report; This is part of a comment from the php bug comment history; *[12 Nov 2007 2:45pm UTC] sam at zoy dot org* Hello, I did read the sources and studied them, and I can confirm that it is a matter of callback jumping to an invalid address. libpq's init_ssl_system() installs callbacks by calling CRYPTO_set_id_callback() and CRYPTO_set_locking_callback(). This function is called each time initialize_SSL() is called (for instance through the PHP pg_connect() function) and does not keep a reference counter, so libpq's destroy_SSL() has no way to know that it should call a destroy_ssl_system() function, and there is no such function anyway. So the callbacks are never removed. But then, upon cleanup, PHP calls zend_shutdown() which properly unloads pgsql.so and therefore the unused libpq. Finally, the zend_shutdown procedure calls zm_shutdown_curl() which in turn calls curl_global_cleanup() which leads to an ERR_free_strings() call and eventually a CRYPTO_lock() call. CRYPTO_lock() checks whether there are any callbacks to call, finds one (the one installed by libpg), calls it, and crashes because libpq was unloaded and hence the callback is no longer in mapped memory. -- Basically postgresql doesn't cancel the callbacks to itself when the pg connection is shut down. So if the libpq library is unloaded before other libraries that use SSL you get a crash as described above. PHP has suggested the fix is to keep a reference counter in libpq so knows when to remove the callbacks. This is a complicated bug, but without real evidence there is no way to go to back to PHP and say it's their fault. Their analysis is relatively comprehensive compared to the feedback that's been posted here so far. I'm not sure how best to setup an environment to replicate the bug in a way I can debug it. And even if I get to the point of nailing it down, I'll just be back asking questions about how you would fix it because I know very little about SSL. All that said, a quick poke in the source of PostgreSQL says that fe-secure.c sets callbacks using CRYPTO_set_xx_callback(...). These are only set in the threaded version it appears. Which is pretty much default in all the installations I encounter. My google research indicated we need to call CRYPTO_set_xx_callback(NULL) when we exit. but that's not done. One idea for a fix is to add a counter to the initialize_ssl function and when destory_ssl is called, decrement the counter. If it reaches 0 then call CRYPT_set_xx_callback(NULL) to remove the callbacks. This is a windows SSL thread that crashes iexplore and testifies to the same problem http://www.mail-archive.com/[EMAIL PROTECTED]/msg53869.html Thoughts? Regards Russell Smith -- 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 with FOR ... LOOP and composite types
Hello.
Seems there is an error when I try to use a table with one field - composite
type, when SELECT QUERY in FOR ... LOOP instruction returns empty result.
Here are steps to reproduce:
CREATE TYPE "t_type" AS (
"a" BIGINT
);
CREATE TABLE"t_table" (
"id" BIGINT NOT NULL,
"t" "t_type",
CONSTRAINT "t_table_pkey" PRIMARY KEY("id")
) WITH OIDS;
CREATE OR REPLACE FUNCTION "t_func" () RETURNS SETOF "t_table" AS
$body$
DECLARE
rec t_table%ROWTYPE;
BEGIN
FOR rec IN
SELECT *
FROM t_table
WHERE 1=0
LOOP
RETURN NEXT rec;
END LOOP;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
SELECT * FROM t_func()
Result:
ERROR: cannot assign non-composite value to a row variable
CONTEXT: PL/pgSQL function "t_func" line 4 at FOR over SELECT rows
Re: [BUGS] Bug with FOR ... LOOP and composite types
Hello
2008/9/1 Oleg Serov <[EMAIL PROTECTED]>:
> Hello.
>
> Seems there is an error when I try to use a table with one field - composite
> type, when SELECT QUERY in FOR ... LOOP instruction returns empty result.
> Here are steps to reproduce:
>
> CREATE TYPE "t_type" AS (
> "a" BIGINT
> );
>
> CREATE TABLE"t_table" (
> "id" BIGINT NOT NULL,
> "t" "t_type",
> CONSTRAINT "t_table_pkey" PRIMARY KEY("id")
> ) WITH OIDS;
>
> CREATE OR REPLACE FUNCTION "t_func" () RETURNS SETOF "t_table" AS
> $body$
> DECLARE
> rec t_table%ROWTYPE;
> BEGIN
> FOR rec IN
> SELECT *
> FROM t_table
> WHERE 1=0
> LOOP
> RETURN NEXT rec;
> END LOOP;
> END;
> $body$
> LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
>
> SELECT * FROM t_func()
>
> Result:
>
> ERROR: cannot assign non-composite value to a row variable
> CONTEXT: PL/pgSQL function "t_func" line 4 at FOR over SELECT rows
ROWTYPE is problem.
postgres=# CREATE OR REPLACE FUNCTION "t_func" () RETURNS SETOF "t_table" AS
postgres-# $body$
postgres$# DECLARE
postgres$# rec record;
postgres$# BEGIN
postgres$# FOR rec IN
postgres$# SELECT *
postgres$# FROM t_table
postgres$# WHERE 1=0
postgres$# LOOP
postgres$# RETURN NEXT rec;
postgres$# END LOOP;
postgres$# END;
postgres$# $body$
postgres-# LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
CREATE FUNCTION
postgres=# select * from t_func();
id | t
+---
(0 rows)
regards
Pavel Stehule
--
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 in RETURN QUERY
Hello all SQL BUG CODE:
BEGIN;
SELECT version(); -- "PostgreSQL 8.3.3 on i686-redhat-linux-gnu, compiled by
GCC gcc (GCC) 4.1.2 20070626 (Red Hat 4.1.2-14)"
CREATE TYPE "buggy_enum_first" AS ENUM ( 'bug1', 'bug2', 'bug3' );
CREATE TABLE "bug_table" (
"id" BIGINT NOT NULL,
"buggy_enum_field" "buggy_enum_first" DEFAULT 'bug1'::buggy_enum_first NOT
NULL,
CONSTRAINT "test_table_pkey" PRIMARY KEY("id")
) WITHOUT OIDS;
CREATE FUNCTION buggy_procedure() RETURNS SETOF bug_table
AS $$
BEGIN
-- @todo hide password
RETURN QUERY (
SELECT *
FROM bug_table
);
END;
$$
LANGUAGE plpgsql STRICT SECURITY DEFINER;
SELECT * FROM buggy_procedure(); -- All Okey
DROP TYPE buggy_enum_first CASCADE;
CREATE TYPE "buggy_enum_second" AS ENUM ( 'bug1', 'bug2', 'bug3' );
ALTER TABLE bug_table ADD COLUMN buggy_enum_field buggy_enum_second;
SELECT * FROM buggy_procedure(); -- Bug
ROLLBACK;
/*NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"test_table_pkey" for table "bug_table"
NOTICE: drop cascades to default for table bug_table column
buggy_enum_fieldNOTICE: drop cascades to table bug_table column
buggy_enum_field
ERROR: structure of query does not match function result type
CONTEXT: PL/pgSQL function "buggy_procedure" line 3 at RETURN QUERY*/
Re: [BUGS] libpq does not manage SSL callbacks properly when other libraries are involved.
Russell Smith wrote: > Alvaro Herrera wrote: > > PoolSnoopy wrote: > > > >> this bug is really some annoyance if you use automatic build environments. > >> I'm using phpunit to run tests and as soon as postgres is involved the php > >> cli environment segfaults at the end. this can be worked around by > >> disabling > >> ssl but it would be great if the underlying bug got fixed. > >> > > > > This is PHP's bug, isn't it? Why are you complaining here > No, this is a problem with the callback/exit functions used by > PostgreSQL. We setup callback functions when we use SSL, if somebody > else uses SSL we can create a problem. Ok, so it seems you're correct; there is more evidence to be found by searching other projects' mailing lists, for example as a starting point http://markmail.org/search/?q=+CRYPTO_set_locking_callback%28NULL%29 -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Bug in RETURN QUERY
Hello
2008/9/1 Oleg Serov <[EMAIL PROTECTED]>:
> Hello all SQL BUG CODE:
> BEGIN;
> SELECT version(); -- "PostgreSQL 8.3.3 on i686-redhat-linux-gnu, compiled by
> GCC gcc (GCC) 4.1.2 20070626 (Red Hat 4.1.2-14)"
> CREATE TYPE "buggy_enum_first" AS ENUM ( 'bug1', 'bug2', 'bug3' );
>
> CREATE TABLE "bug_table" (
> "id" BIGINT NOT NULL,
> "buggy_enum_field" "buggy_enum_first" DEFAULT 'bug1'::buggy_enum_first NOT
> NULL,
> CONSTRAINT "test_table_pkey" PRIMARY KEY("id")
> ) WITHOUT OIDS;
>
>
> CREATE FUNCTION buggy_procedure() RETURNS SETOF bug_table
> AS $$
> BEGIN
> -- @todo hide password
> RETURN QUERY (
> SELECT *
> FROM bug_table
> );
> END;
> $$
> LANGUAGE plpgsql STRICT SECURITY DEFINER;
>
> SELECT * FROM buggy_procedure(); -- All Okey
> DROP TYPE buggy_enum_first CASCADE;
> CREATE TYPE "buggy_enum_second" AS ENUM ( 'bug1', 'bug2', 'bug3' );
> ALTER TABLE bug_table ADD COLUMN buggy_enum_field buggy_enum_second;
> SELECT * FROM buggy_procedure(); -- Bug
> ROLLBACK;
> /*NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
> "test_table_pkey" for table "bug_table"
>
> NOTICE: drop cascades to default for table bug_table column
> buggy_enum_fieldNOTICE: drop cascades to table bug_table column
> buggy_enum_field
> ERROR: structure of query does not match function result type
> CONTEXT: PL/pgSQL function "buggy_procedure" line 3 at RETURN QUERY*/
this isn't bug, it's feature. Informations stored in execution plan
are broken, when some references to removed objects are stored in plan
- objects aren't stored by name, but by object id. So, when you drop
any object, then you have to finish session. Note: actually only drop
of table emits plan cache invalidation signal.
Regards
Pavel Stehule
--
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 #4389: FATAL: could not reattach to shared memory (key=1804, addr=018E0000): 487
The following bug has been logged online: Bug reference: 4389 Logged by: could not reattach to shared memory Email address: [EMAIL PROTECTED] PostgreSQL version: 8.3.3-1 Operating system: any 8.3.* Description:FATAL: could not reattach to shared memory (key=1804, addr=018E): 487 Details: This error came week ago. >From that 'black' day I can not use Postgre. I have reinstalled several 8.3.* versions (including last version with vcredist_x86.exe) and nothing helps me. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Bug with FOR ... LOOP and composite types
"Pavel Stehule" <[EMAIL PROTECTED]> writes: > 2008/9/1 Oleg Serov <[EMAIL PROTECTED]>: >> Seems there is an error when I try to use a table with one field - composite >> type, when SELECT QUERY in FOR ... LOOP instruction returns empty result. > ROWTYPE is problem. I think it actually is a bug. exec_for_query tries to set the target to null this way: exec_move_row(estate, rec, row, NULL, tuptab->tupdesc); and exec_move_row thinks it doesn't have to present a valid "valtype" to exec_assign_value when it's assigning a made-up null, and that fails when the target is of PLPGSQL_DTYPE_ROW type (looks like it'd fail for REC type too, but ROW is the case here). We could work around the particular issue by moving the type_is_rowtype() tests down so they're not done for a null source value, but I think that's just a hack. A cleaner fix would be to teach exec_move_row to present the correct column type in all cases. 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
Re: [BUGS] Bug in RETURN QUERY
"Pavel Stehule" <[EMAIL PROTECTED]> writes: > 2008/9/1 Oleg Serov <[EMAIL PROTECTED]>: >> ERROR: structure of query does not match function result type >> CONTEXT: PL/pgSQL function "buggy_procedure" line 3 at RETURN QUERY*/ > this isn't bug, it's feature. No, it's a bug, and it's not related to plan caching at all --- even if you start a fresh session the error persists. The problem is that plpgsql isn't very good at dealing with rowtypes that contain dropped columns. Unfortunately Oleg shouldn't hold his breath waiting for a fix, because it's not trivial. In this example, the function would need to return a three-column tuple (id, dropped-column, buggy_enum_field) but the SELECT is only giving it two columns. There isn't anything in plpgsql that has the ability to convert a tuple to add dropped columns in the right places. I think we'd consider adding such functionality as a new feature not a back-patchable bug fix. The best near-term workaround would be to handle changes like this by means of ALTER COLUMN TYPE rather than dropping and re-adding columns. 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
Re: [BUGS] BUG #4389: FATAL: could not reattach to shared memory (key=1804, addr=018E0000): 487
could not reattach to shared memory napsal(a): The following bug has been logged online: Bug reference: 4389 Logged by: could not reattach to shared memory Email address: [EMAIL PROTECTED] PostgreSQL version: 8.3.3-1 Operating system: any 8.3.* Description:FATAL: could not reattach to shared memory (key=1804, addr=018E): 487 Details: This error came week ago. From that 'black' day I can not use Postgre. I have reinstalled several 8.3.* versions (including last version with vcredist_x86.exe) and nothing helps me. try to remove postgesql.pid file in the data directory. Zdenek -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Bug with FOR ... LOOP and composite types
But if there are some records in t_table and we romove WHERE 1=0, we will
have
ERROR: wrong record type supplied in RETURN NEXT CONTEXT: PL/pgSQL function
"t_func" line 9 at RETURN NEXT
2008/9/1 Pavel Stehule <[EMAIL PROTECTED]>
> Hello
>
> 2008/9/1 Oleg Serov <[EMAIL PROTECTED]>:
> > Hello.
> >
> > Seems there is an error when I try to use a table with one field -
> composite
> > type, when SELECT QUERY in FOR ... LOOP instruction returns empty result.
> > Here are steps to reproduce:
> >
> > CREATE TYPE "t_type" AS (
> > "a" BIGINT
> > );
> >
> > CREATE TABLE"t_table" (
> > "id" BIGINT NOT NULL,
> > "t" "t_type",
> > CONSTRAINT "t_table_pkey" PRIMARY KEY("id")
> > ) WITH OIDS;
> >
> > CREATE OR REPLACE FUNCTION "t_func" () RETURNS SETOF "t_table" AS
> > $body$
> > DECLARE
> > rec t_table%ROWTYPE;
> > BEGIN
> > FOR rec IN
> > SELECT *
> > FROM t_table
> > WHERE 1=0
> > LOOP
> > RETURN NEXT rec;
> > END LOOP;
> > END;
> > $body$
> > LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
> >
> > SELECT * FROM t_func()
> >
> > Result:
> >
> > ERROR: cannot assign non-composite value to a row variable
> > CONTEXT: PL/pgSQL function "t_func" line 4 at FOR over SELECT rows
>
> ROWTYPE is problem.
>
> postgres=# CREATE OR REPLACE FUNCTION "t_func" () RETURNS SETOF "t_table"
> AS
> postgres-# $body$
> postgres$# DECLARE
> postgres$# rec record;
> postgres$# BEGIN
> postgres$# FOR rec IN
> postgres$# SELECT *
> postgres$# FROM t_table
> postgres$# WHERE 1=0
> postgres$# LOOP
> postgres$# RETURN NEXT rec;
> postgres$# END LOOP;
> postgres$# END;
> postgres$# $body$
> postgres-# LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY
> INVOKER;
> CREATE FUNCTION
> postgres=# select * from t_func();
> id | t
> +---
> (0 rows)
>
> regards
> Pavel Stehule
>
[BUGS] BUG #4392: initdb doen't work with options -U username and -W
The following bug has been logged online: Bug reference: 4392 Logged by: Andrea Villardino Email address: [EMAIL PROTECTED] PostgreSQL version: 8.3.3 Operating system: windows server 2003(or vista) Description:initdb doen't work with options -U username and -W Details: if i do an installation with: msiexec /i "%PATH_DB_INSTALLER%postgresql-8.3-int.msi" /l*v %DB_INSTALL_DIR_LOG%postgres_install.log /qr INTERNALLAUNCH=1 ADDLOCAL=server,psql SERVICEDOMAIN=%COMPUTERNAME% SERVICEACCOUNT=%DB_SERVICE_ACCOUNT% SERVICEPASSWORD=%DB_SERVICE_PASSWORD% SUPERUSER=%DB_SUPERUSER_ACCOUNT% SUPERPASSWORD=%DB_SUPERUSER_PASSWORD% ENCODING=%DB_SERVER_ENCODING% LOCALE=%DB_SERVER_LOCALE% it works. But when I try to divede msiexec from inidb it doesn't work. In particular: "C:\\Program Files\\postgresql\\8.3\\bin\\initdb.exe" --pgdata="C:\\Program Files\\postgresql\\8.3\\data" --encoding=UNICODE --locale=C It works. But: "C:\\Program Files\\postgresql\\8.3\\bin\\initdb.exe" --pgdata="C:\\Program Files\\postgresql\\8.3\\data" --encoding=UNICODE --locale=C -U andrea -W Doesn't work because when i try to start up the postgres service there are a lot of error in the application event viewer saying: FATAL: role "postgres" does not exist Even if the inidb output is ok: C:\WINDOWS>"C:\\Program Files\\postgresql\\8.3\\bin\\initdb.exe" --pgdata="C:\\Program Files\\postgresql\\8.3\\data" --encoding=UNICODE --locale=C -U andrea -W The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locale C. The default text search configuration will be set to "english". fixing permissions on existing directory C:/Program Files/postgresql/8.3/data ... ok creating subdirectories ... ok selecting default max_connections ... 100 selecting default shared_buffers/max_fsm_pages ... 32MB/204800 creating configuration files ... ok creating template1 database in C:/Program Files/postgresql/8.3/data/base/1 ... ok initializing pg_authid ... ok Enter new superuser password: Enter it again: setting password ... ok initializing dependencies ... ok creating system views ... ok loading system objects' descriptions ... ok creating conversions ... ok creating dictionaries ... ok setting privileges on built-in objects ... ok creating information schema ... ok vacuuming database template1 ... ok copying template1 to template0 ... ok copying template1 to postgres ... ok WARNING: enabling "trust" authentication for local connections You can change this by editing pg_hba.conf or using the -A option the next time you run initdb. Success. You can now start the database server using: "C:\\Program Files\\postgresql\\8.3\\bin\postgres" -D "C:/Program Files/postgresql/8.3/data" or "C:\\Program Files\\postgresql\\8.3\\bin\pg_ctl" -D "C:/Program Files/postgresql/8.3/data" -l logfile start C:\WINDOWS> -- 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 #4391: initdb doen't work with options -U username and -W
The following bug has been logged online: Bug reference: 4391 Logged by: Andrea Villardino Email address: [EMAIL PROTECTED] PostgreSQL version: 8.3.3 Operating system: windows server 2003(or vista). Description:initdb doen't work with options -U username and -W Details: if i do an installation with: msiexec /i "%PATH_DB_INSTALLER%postgresql-8.3-int.msi" /l*v %DB_INSTALL_DIR_LOG%postgres_install.log /qr INTERNALLAUNCH=1 ADDLOCAL=server,psql SERVICEDOMAIN=%COMPUTERNAME% SERVICEACCOUNT=%DB_SERVICE_ACCOUNT% SERVICEPASSWORD=%DB_SERVICE_PASSWORD% SUPERUSER=%DB_SUPERUSER_ACCOUNT% SUPERPASSWORD=%DB_SUPERUSER_PASSWORD% ENCODING=%DB_SERVER_ENCODING% LOCALE=%DB_SERVER_LOCALE% it works. But when I try to divede msiexec from inidb it doesn't work. In particular: "C:\\Program Files\\postgresql\\8.3\\bin\\initdb.exe" --pgdata="C:\\Program Files\\postgresql\\8.3\\data" --encoding=UNICODE --locale=C It works. But: "C:\\Program Files\\postgresql\\8.3\\bin\\initdb.exe" --pgdata="C:\\Program Files\\postgresql\\8.3\\data" --encoding=UNICODE --locale=C -U andrea -W Doesn't work because when i try to start up the postgres service there are a lot of error in the application event viewer saying: FATAL: role "postgres" does not exist Even if the inidb output is ok: C:\WINDOWS>"C:\\Program Files\\postgresql\\8.3\\bin\\initdb.exe" --pgdata="C:\\Program Files\\postgresql\\8.3\\data" --encoding=UNICODE --locale=C -U andrea -W The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locale C. The default text search configuration will be set to "english". fixing permissions on existing directory C:/Program Files/postgresql/8.3/data ... ok creating subdirectories ... ok selecting default max_connections ... 100 selecting default shared_buffers/max_fsm_pages ... 32MB/204800 creating configuration files ... ok creating template1 database in C:/Program Files/postgresql/8.3/data/base/1 ... ok initializing pg_authid ... ok Enter new superuser password: Enter it again: setting password ... ok initializing dependencies ... ok creating system views ... ok loading system objects' descriptions ... ok creating conversions ... ok creating dictionaries ... ok setting privileges on built-in objects ... ok creating information schema ... ok vacuuming database template1 ... ok copying template1 to template0 ... ok copying template1 to postgres ... ok WARNING: enabling "trust" authentication for local connections You can change this by editing pg_hba.conf or using the -A option the next time you run initdb. Success. You can now start the database server using: "C:\\Program Files\\postgresql\\8.3\\bin\postgres" -D "C:/Program Files/postgresql/8.3/data" or "C:\\Program Files\\postgresql\\8.3\\bin\pg_ctl" -D "C:/Program Files/postgresql/8.3/data" -l logfile start C:\WINDOWS> -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Bug with FOR ... LOOP and composite types
"Oleg Serov" <[EMAIL PROTECTED]> writes: > But if there are some records in t_table and we romove WHERE 1=0, we will > have > ERROR: wrong record type supplied in RETURN NEXT CONTEXT: PL/pgSQL function > "t_func" line 9 at RETURN NEXT I couldn't reproduce that here, at least not with versions newer than 8.0. Maybe you were testing a case that also involved dropped columns? 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
Re: [BUGS] Problem with planer
[EMAIL PROTECTED] wrote: > select ats.id, ap.value from akh_test_suit ats > LEFT JOIN akh_properties ap on ap.ID = ats.test_suit_type_id > where ats.ID = 472 > > id | value > 472 | 472 > ID -- integer > value -- text > > > select * from akh_test_suit ats > LEFT JOIN akh_properties ap on ap.ID = ats.test_suit_type_id > where ats.ID = 472 and ap.value::integer = ats.ID > > ERROR: invalid input syntax for integer: "--username sergeiz > --password sergeiz --non-interactive svn://sergeiz" > where clauses are not evaluated in the order written. There is no guarantee that ats.ID = 472 will be evaluated before ap.value::integer. You can't write SQL queries like they are a piece of programming logic, they are not always evaluated in order and may not be evaluated in the order you wrote them. My first guess it to write it up as a subquery; select * from (select * from akh_test_suit ats LEFT JOIN akh_properties ap on ap.ID = ats.test_suit_type_id where ats.ID = 472) as sub WHERE value::integer = ID; Or unless you are specifically needing the performance, you could just allow pg to automatically coerce ats.ID to text. Regards Russell. > akh_properties.values has non numeric values, but those rows do not > (MUST NOT) participate in results as showed in first query > Why PG check them? -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
