Re: Problems with Set Returning Functions (SRFs)

Helloo !

We have a database that contains data that we need to Parse.

Ideally I would like write a C-function, ParseData, and run

select ParseData([data_column]) from datatable where date='2005-05-05';

and have it return 5 columns with the parsed data. Each row in Data_column
will potentially create multiple output-rows.
I did some research and SRF seems to be the solution (?). After playing
around with the TestPassByVal example on the postgres

website (http://www.postgresql.org/docs/8.0/interactive/xfunc-c.html) I'v
ran into troubles.

Here is the type definion

CREATE TYPE __testpassbyval AS (f1 integer, f2 integer, f3 integer);

CREATE OR REPLACE FUNCTION testpassbyval(integer, integer) RETURNS SETOF
__testpassbyval
AS 'filename', 'testpassbyval'
LANGUAGE C IMMUTABLE STRICT;

First paramter is the number of rows the function returns. Second Parameter
is the multiplier.

First we Try

secom=# select testpassbyval(2, 5);
testpassbyval
---------------
(5,10,15)
(5,10,15)
(2 rows)

Then we can extract the columns using

secom=# select f1, f2, f3 from testpassbyval(2, 5);
f1 | f2 | f3
----+----+----
5 | 10 | 15
5 | 10 | 15
(2 rows)

So far so good.

But What I want is to feed the testpassbyval function with data from a
column (data_column)

Creating a test table with column data_column having integers from 1 trew 9
we get

secom=# select testpassbyval(2, data_column) from datatable;
testpassbyval
---------------
(1,2,3)
(1,2,3)
(2,4,6)
(2,4,6)
(3,6,9)
(3,6,9)
(4,8,12)
(4,8,12)
(5,10,15)
(5,10,15)
(6,12,18)
(6,12,18)
(7,14,21)
(7,14,21)
(8,16,24)
(8,16,24)
(9,18,27)
(9,18,27)
(18 rows)

Looking good. Now I try to extract the columns

secom=# select f1, f2, f3 from testpassbyval(1, (Select number1 from test));
ERROR: more than one row returned by a subquery used as an expression

This is where I fail. Am I even on the right path here ? Writing the actual
parsing function will be easy once I have a working concept.

Any ideas ?

Thanks a lot

/Otto Blomqvist

I'm Running PSQL 8.0.0 on Linux 8.0

"Otto Blomqvist" <o(dot)blomqvist(at)secomintl(dot)com> writes:
> secom=# select f1, f2, f3 from testpassbyval(1, (Select number1 from test));
> ERROR: more than one row returned by a subquery used as an expression

In 8.0 I think it'd work to do

select (x).f1, (x).f2, (x).f3 from
(select testpassbyval(1, number1) as x from test) ss;

regards, tom lane

Ofcourse this works perfectly !

Thanks a lot Tom !

Just curious, is this (x)-"trick" in the postgres manual somewhere ? Just
just common SQL guru knowledge ? ;)

/Otto Blomqvist

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote in message
news:5852(dot)1112819444(at)sss(dot)pgh(dot)pa(dot)us(dot)(dot)(dot)
> "Otto Blomqvist" <o(dot)blomqvist(at)secomintl(dot)com> writes:
> > secom=# select f1, f2, f3 from testpassbyval(1, (Select number1 from
test));
> > ERROR: more than one row returned by a subquery used as an expression
>
> In 8.0 I think it'd work to do
>
> select (x).f1, (x).f2, (x).f3 from
> (select testpassbyval(1, number1) as x from test) ss;
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>

Hello,

Just installed pg 8.0.1-r3 and now phppgadmin and pgadmin3 die with a
cryptic error message.
I've traced it to the following query to get information about the
database :

SELECT pdb.datname AS datname, pu.usename AS datowner,
pg_encoding_to_char(encoding) AS datencoding,
(SELECT description FROM pg_description pd WHERE pdb.oid=pd.objoid) AS
datcomment,
(SELECT spcname FROM pg_catalog.pg_tablespace pt WHERE
pt.oid=pdb.dattablespace) AS tablespace
FROM pg_database pdb, pg_user pu
WHERE pdb.datdba = pu.usesysid AND NOT pdb.datistemplate
ORDER BY pdb.datname;

This query looks right to me and pg 8.0.0 does it fine, while 8.0.1-r3
says :

Error : Unexpected right parenthesis.

This is not very helpful...
What's going on ?

I've downgraded to 8.0.0 in the meantime.

Why don't you try PG Lightning Admin and see if you get the same error.
http://www.amsoftwaredesign.com

I have been using it with 8.01 without issues.

T

PFC <lists(at)boutiquenumerique(dot)com> writes:
> I've traced it to the following query to get information about the
> database :

> SELECT pdb.datname AS datname, pu.usename AS datowner,
> pg_encoding_to_char(encoding) AS datencoding,
> (SELECT description FROM pg_description pd WHERE pdb.oid=pd.objoid) AS
> datcomment,
> (SELECT spcname FROM pg_catalog.pg_tablespace pt WHERE
> pt.oid=pdb.dattablespace) AS tablespace
> FROM pg_database pdb, pg_user pu
> WHERE pdb.datdba = pu.usesysid AND NOT pdb.datistemplate
> ORDER BY pdb.datname;

Works for me.

> This query looks right to me and pg 8.0.0 does it fine, while 8.0.1-r3
> says :
> Error : Unexpected right parenthesis.

Hmm; the only occurrence of that string in the sources is in
nodes/read.c, which AFAIK would never be invoked for a query entered
straight from the client. What could trigger it would be trying to
read a rule that is misformatted for some reason. Maybe you have
a damaged pg_user view definition --- does "select * from pg_user"
fail the same way?

regards, tom lane

On Wed, Apr 06, 2005 at 03:31:45PM -0700, Otto Blomqvist wrote:
>
> Just curious, is this (x)-"trick" in the postgres manual somewhere ? Just
> just common SQL guru knowledge ? ;)

I think the relevant documentation is "Field Selection" in the
"Value Expressions" section of the "SQL Syntax" chapter.

http://www.postgresql.org/docs/8.0/interactive/sql-expressions.html#AEN1642

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

> Hmm; the only occurrence of that string in the sources is in
> nodes/read.c, which AFAIK would never be invoked for a query entered
> straight from the client. What could trigger it would be trying to
> read a rule that is misformatted for some reason. Maybe you have
> a damaged pg_user view definition --- does "select * from pg_user"
> fail the same way?

I installed pg 8.0.0 over 8.0.1-r3 (without touching the data directory)
and then it worked again, so I don't think it has to do with the data
being broken. I didn't do createdb or anything. I used gentoo emerge.

If you need it for debug, I can reinstall 8.0.1-r3 and see if it still
fails.
Can you get phppgadmin to work on this latest version ?

Thank you,
P.F. Caillaud

On Apr 6, 2005, at 2:53 PM, Otto Blomqvist wrote:
>
> secom=# select f1, f2, f3 from testpassbyval(1, (Select number1 from
> test));
> ERROR: more than one row returned by a subquery used as an expression
>
> This is where I fail. Am I even on the right path here ? Writing the
> actual
> parsing function will be easy once I have a working concept.
>

How about (untested):

select f1, f2, f3 from (
select testpassbyval(1,(
select number1 from test)
)
) a;

Sean

PFC <lists(at)boutiquenumerique(dot)com> writes:
>> Hmm; the only occurrence of that string in the sources is in
>> nodes/read.c, which AFAIK would never be invoked for a query entered
>> straight from the client. What could trigger it would be trying to
>> read a rule that is misformatted for some reason. Maybe you have
>> a damaged pg_user view definition --- does "select * from pg_user"
>> fail the same way?

> I installed pg 8.0.0 over 8.0.1-r3 (without touching the data directory)
> and then it worked again, so I don't think it has to do with the data
> being broken. I didn't do createdb or anything. I used gentoo emerge.

Well, that implies some file-format-level incompatibility between 8.0.0
and 8.0.1-r3, which would be quite disturbing ... especially if we had
more than one report of it. As is, I suspect it means you had a broken
build of 8.0.1-r3 for some reason. One possibility is a different set
of configuration parameters from what was used to build 8.0.0 (though
offhand I can't think of anything that would yield this particular
failure).

You might try comparing the results of
select ev_action from pg_rewrite where ev_class = 'pg_user'::regclass;
from the two builds.

regards, tom lane

> You might try comparing the results of
> select ev_action from pg_rewrite where ev_class = 'pg_user'::regclass;
> from the two builds.
>
> regards, tom lane
>

Well...

I'll spare your eyes and tell you right away that the results are
identical... they're at the bottom of the email.

I tried recompiling postgres 8.0.1-r2 with :
CFLAGS="-O2 -pipe -march=pentium3"
which is quite conservative (I have a Pentium-M, this is my dev laptop).

$ gcc --version
gcc (GCC) 3.3.5 (Gentoo Linux 3.3.5-r1, ssp-3.3.2-3, pie-8.7.7.1)

And I still get (in 8.0.1-r2) :

test=> SELECT pdb.datname AS datname, pu.usename AS datowner,
pg_encoding_to_char(encoding) AS datencoding,
test-> (SELECT description FROM pg_description pd WHERE pdb.oid=pd.objoid)
AS datcomment,
test-> (SELECT spcname FROM pg_catalog.pg_tablespace pt WHERE
pt.oid=pdb.dattablespace) AS tablespace
test-> FROM pg_database pdb, pg_user pu
test-> WHERE pdb.datdba = pu.usesysid AND NOT pdb.datistemplate
test-> ORDER BY pdb.datname;
ERREUR: unexpected right parenthesis

Let's try something else :

postgresql $ createuser -P peufeu
Entrez le mot de passe pour le nouvel utilisateur :
Entrez-le de nouveau :
Le nouvel utilisateur a-t'il le droit de créer des bases de données ?
(y/n) o
(note that I have to type "o" and not "y" as it's in French... although it
still displays y/n, "y" means "no" ! it bit me !)
Le nouvel utilisateur a-t'il le droit de créer des utilisateurs ? (y/n) n
Mot de passe :
CREATE USER

peufeu $ createdb
Mot de passe :
CREATE DATABASE
peufeu(at)nyuu peufeu $ psql
Mot de passe :
Bienvenue dans psql 8.0.1, l'interface interactive de PostgreSQL.

Tapez: \copyright pour les termes de distribution
\h pour l'aide-mémoire sur les commandes SQL
\? pour l'aide-mémoire sur les commandes internes
\g ou terminez avec un point-virgule pour exécuter une requête
\q pour quitter

peufeu=> SELECT pdb.datname AS datname, pu.usename AS datowner,
pg_encoding_to_char(encoding) AS datencoding,
peufeu-> (SELECT description FROM pg_description pd WHERE
pdb.oid=pd.objoid) AS datcomment,
peufeu-> (SELECT spcname FROM pg_catalog.pg_tablespace pt WHERE
pt.oid=pdb.dattablespace) AS tablespace
peufeu-> FROM pg_database pdb, pg_user pu
peufeu-> WHERE pdb.datdba = pu.usesysid AND NOT pdb.datistemplate
peufeu-> ORDER BY pdb.datname;
ERREUR: unexpected right parenthesis

ka-blam.

I checked there was no invisible UTF-8 character inside the query...
there isn't, everything's normal...
I cut bits of the query, trying to get to the bug, and to my surprise, I
got :

peufeu=> SELECT pdb.datname FROM pg_database pdb, pg_user pu;
ERREUR: unexpected right parenthesis

Which is interesting, as there is no parenthesis in this query ;)
I'll copypaste the rest of the session :

peufeu=> SELECT pdb.datname FROM pg_database pdb, pg_user pux;
ERREUR: unexpected right parenthesis
peufeu=> SELECT pdb.datname FROM pg_database pdb, pg_user pu;
ERREUR: unexpected right parenthesis
peufeu=> SELECT pdb.datname FROM pg_database pdb, pg_user;
ERREUR: unexpected right parenthesis
peufeu=> SELECT foo.datname FROM pg_database foo, pg_user;
ERREUR: unexpected right parenthesis
peufeu=> SELECT foo.datname FROM pg_database foo;
datname
-----------
test
peufeu
template1
template0
(4 lignes)

peufeu=> SELECT foo.datname FROM pg_user, pg_database foo;
ERREUR: unexpected right parenthesis
peufeu=> SELECT * FROM pg_user LIMIT 1;
ERREUR: unexpected right parenthesis
peufeu=> SELECT * FROM pg_user LIMIT 1;
pg_user
peufeu=> SELECT * FROM pg_user LIMIT 1;
ERREUR: unexpected right parenthesis
peufeu=> SELECT * FROM pg_user;
ERREUR: unexpected right parenthesis
peufeu=> SELECT 1 FROM pg_user;
ERREUR: unexpected right parenthesis

Seems I have a cursed table.
I login as postgres :

$ psql -U postgres peufeu
Mot de passe :
Bienvenue dans psql 8.0.1, l'interface interactive de PostgreSQL.

Tapez: \copyright pour les termes de distribution
\h pour l'aide-mémoire sur les commandes SQL
\? pour l'aide-mémoire sur les commandes internes
\g ou terminez avec un point-virgule pour exécuter une requête
\q pour quitter

peufeu=# SELECT 1 FROM pg_user;
ERREUR: unexpected right parenthesis
peufeu=# ANALYZE VERBOSE pg_user;
ERREUR: unexpected right parenthesis
peufeu=# ANALYZE VERBOSE "pg_user";
ERREUR: unexpected right parenthesis

peufeu(at)nyuu peufeu $ echo "ANALYZE VERBOSE" | psql -U postgres peufeu 2>log
Mot de passe :
ANALYZE
peufeu(at)nyuu peufeu $ grep pg_catalog log
INFO: Analyse de "pg_catalog.pg_shadow"
INFO: Analyse de "pg_catalog.pg_opclass"
INFO: Analyse de "pg_catalog.pg_am"
INFO: Analyse de "pg_catalog.pg_amop"
etc...

peufeu(at)nyuu peufeu $ grep user log
peufeu(at)nyuu peufeu $

I'm totally perplexed. Have you got any idea ?
I'm itching to do an initdb...

8.0.0 :
({QUERY :commandType 1 :querySource 0 :canSetTag true :utilityStmt <>
:resultRelation 0 :into <> :hasAggs false :hasSubLinks false :rtable ({RTE
:alias {ALIAS :aliasname *OLD* :colnames <>} :eref {ALIAS :aliasname *OLD*
:colnames ("usename""usesysid" "usecreatedb" "usesuper" "usecatupd"
"passwd" "valuntil" "useconfig")} :rtekind 0 :relid 16762 :inh false
:inFromCl false :requiredPerms 0 :checkAsUser 1} {RTE :alias {ALIAS
:aliasname *NEW* :colnames <>} :eref {ALIAS :aliasname *NEW* :colnames
("usename" "usesysid" "usecreatedb" "usesuper" "usecatupd" "passwd"
"valuntil" "useconfig")} :rtekind 0 :relid 16762 :inh false :inFromCl
false :requiredPerms 0 :checkAsUser 1} {RTE :alias <> :eref {ALIAS
:aliasname pg_shadow :colnames ("usename" "usesysid" "usecreatedb"
"usesuper" "usecatupd" "passwd" "valuntil" "useconfig")} :rtekind 0 :relid
1260 :inh true :inFromCl true :requiredPerms 2 :checkAsUser 1}) :jointree
{FROMEXPR :fromlist ({RANGETBLREF :rtindex 3}) :quals <>} :rowMarks <>
:targetList ({TARGETENTRY :resdom {RESDOM :resno 1 :restype 19 :restypmod
-1 :resname usename :ressortgroupref 0 :resorigtbl 1260 :resorigcol 1
:resjunk false} :expr {VAR :varno 3 :varattno 1 :vartype 19 :vartypmod -1
:varlevelsup 0 :varnoold 3 :varoattno 1}} {TARGETENTRY :resdom {RESDOM
:resno 2 :restype 23 :restypmod -1 :resname usesysid :ressortgroupref 0
:resorigtbl 1260 :resorigcol 2 :resjunk false} :expr {VAR :varno 3
:varattno 2 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 3
:varoattno 2}} {TARGETENTRY :resdom {RESDOM :resno 3 :restype 16
:restypmod -1 :resname usecreatedb :ressortgroupref 0 :resorigtbl 1260
:resorigcol 3 :resjunk false} :expr {VAR :varno 3 :varattno 3 :vartype 16
:vartypmod -1 :varlevelsup 0 :varnoold 3 :varoattno 3}} {TARGETENTRY
:resdom {RESDOM :resno 4 :restype 16 :restypmod -1 :resname usesuper
:ressortgroupref 0 :resorigtbl 1260 :resorigcol 4 :resjunk false} :expr
{VAR :varno 3 :varattno 4 :vartype 16 :vartypmod -1 :varlevelsup 0
:varnoold 3 :varoattno 4}} {TARGETENTRY :resdom {RESDOM :resno 5 :restype
16 :restypmod -1 :resname usecatupd :ressortgroupref 0 :resorigtbl 1260
:resorigcol 5 :resjunk false} :expr {VAR :varno 3 :varattno 5:vartype 16
:vartypmod -1 :varlevelsup 0 :varnoold 3 :varoattno 5}} {TARGETENTRY
:resdom {RESDOM :resno 6 :restype 25 :restypmod -1 :resname passwd
:ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false} :expr
{CONST :consttype 25 :constlen -1 :constbyval false :constisnull false
:constvalue 12 [ 12 0 0 0 42 42 42 42 42 42 42 42 ]}} {TARGETENTRY
:resdom{RESDOM :resno 7 :restype 702 :restypmod -1 :resname valuntil
:ressortgroupref 0 :resorigtbl 1260 :resorigcol 7 :resjunkfalse} :expr
{VAR :varno 3 :varattno 7 :vartype 702 :vartypmod -1 :varlevelsup 0
:varnoold 3 :varoattno 7}} {TARGETENTRY:resdom {RESDOM :resno 8 :restype
1009 :restypmod -1 :resname useconfig :ressortgroupref 0 :resorigtbl 1260
:resorigcol 8 :resjunk false} :expr {VAR :varno 3 :varattno 8 :vartype
1009 :vartypmod -1 :varlevelsup 0 :varnoold 3 :varoattno 8}}) :groupClause
<> :havingQual <> :distinctClause <> :sortClause <> :limitOffset <>
:limitCount <> :setOperations <> :resultRelations <>})

8.0.1-r2:
({QUERY :commandType 1 :querySource 0 :canSetTag true :utilityStmt <>
:resultRelation 0 :into <> :hasAggs false :hasSubLinks false :rtable ({RTE
:alias {ALIAS :aliasname *OLD* :colnames <>} :eref {ALIAS :aliasname *OLD*
:colnames ("usename""usesysid" "usecreatedb" "usesuper" "usecatupd"
"passwd" "valuntil" "useconfig")} :rtekind 0 :relid 16762 :inh false
:inFromCl false :requiredPerms 0 :checkAsUser 1} {RTE :alias {ALIAS
:aliasname *NEW* :colnames <>} :eref {ALIAS :aliasname *NEW* :colnames
("usename" "usesysid" "usecreatedb" "usesuper" "usecatupd" "passwd"
"valuntil" "useconfig")} :rtekind 0 :relid 16762 :inh false :inFromCl
false :requiredPerms 0 :checkAsUser 1} {RTE :alias <> :eref {ALIAS
:aliasname pg_shadow :colnames ("usename" "usesysid" "usecreatedb"
"usesuper" "usecatupd" "passwd" "valuntil" "useconfig")} :rtekind 0 :relid
1260 :inh true :inFromCl true :requiredPerms 2 :checkAsUser 1}) :jointree
{FROMEXPR :fromlist ({RANGETBLREF :rtindex 3}) :quals <>} :rowMarks <>
:targetList ({TARGETENTRY :resdom {RESDOM :resno 1 :restype 19 :restypmod
-1 :resname usename :ressortgroupref 0 :resorigtbl 1260 :resorigcol 1
:resjunk false} :expr {VAR :varno 3 :varattno 1 :vartype 19 :vartypmod -1
:varlevelsup 0 :varnoold 3 :varoattno 1}} {TARGETENTRY :resdom {RESDOM
:resno 2 :restype 23 :restypmod -1 :resname usesysid :ressortgroupref 0
:resorigtbl 1260 :resorigcol 2 :resjunk false} :expr {VAR :varno 3
:varattno 2 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 3
:varoattno 2}} {TARGETENTRY :resdom {RESDOM :resno 3 :restype 16
:restypmod -1 :resname usecreatedb :ressortgroupref 0 :resorigtbl 1260
:resorigcol 3 :resjunk false} :expr {VAR :varno 3 :varattno 3 :vartype 16
:vartypmod -1 :varlevelsup 0 :varnoold 3 :varoattno 3}} {TARGETENTRY
:resdom {RESDOM :resno 4 :restype 16 :restypmod -1 :resname usesuper
:ressortgroupref 0 :resorigtbl 1260 :resorigcol 4 :resjunk false} :expr
{VAR :varno 3 :varattno 4 :vartype 16 :vartypmod -1 :varlevelsup 0
:varnoold 3 :varoattno 4}} {TARGETENTRY :resdom {RESDOM :resno 5 :restype
16 :restypmod -1 :resname usecatupd :ressortgroupref 0 :resorigtbl 1260
:resorigcol 5 :resjunk false} :expr {VAR :varno 3 :varattno 5:vartype 16
:vartypmod -1 :varlevelsup 0 :varnoold 3 :varoattno 5}} {TARGETENTRY
:resdom {RESDOM :resno 6 :restype 25 :restypmod -1 :resname passwd
:ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false} :expr
{CONST :consttype 25 :constlen -1 :constbyval false :constisnull false
:constvalue 12 [ 12 0 0 0 42 42 42 42 42 42 42 42 ]}} {TARGETENTRY
:resdom{RESDOM :resno 7 :restype 702 :restypmod -1 :resname valuntil
:ressortgroupref 0 :resorigtbl 1260 :resorigcol 7 :resjunkfalse} :expr
{VAR :varno 3 :varattno 7 :vartype 702 :vartypmod -1 :varlevelsup 0
:varnoold 3 :varoattno 7}} {TARGETENTRY:resdom {RESDOM :resno 8 :restype
1009 :restypmod -1 :resname useconfig :ressortgroupref 0 :resorigtbl 1260
:resorigcol 8 :resjunk false} :expr {VAR :varno 3 :varattno 8 :vartype
1009 :vartypmod -1 :varlevelsup 0 :varnoold 3 :varoattno 8}}) :groupClause
<> :havingQual <> :distinctClause <> :sortClause <> :limitOffset <>
:limitCount <> :setOperations <> :resultRelations <>})

PFC <lists(at)boutiquenumerique(dot)com> writes:
>> You might try comparing the results of
>> select ev_action from pg_rewrite where ev_class = 'pg_user'::regclass;
>> from the two builds.

> I'll spare your eyes and tell you right away that the results are
> identical... they're at the bottom of the email.

Hmm. Your data matches mine except for several spots where words are run
together:

> :colnames ("usename""usesysid" "usecreatedb" "usesuper" "usecatupd"
^^^^^^^^^^^^^^^^^^^
> :resorigcol 5 :resjunk false} :expr {VAR :varno 3 :varattno 5:vartype 16
^^^^^^^^^
> :resdom{RESDOM :resno 7 :restype 702 :restypmod -1 :resname valuntil
^^^^^^^^^^^^^^
> :ressortgroupref 0 :resorigtbl 1260 :resorigcol 7 :resjunkfalse} :expr
^^^^^^^^^^^^^^
> :varnoold 3 :varoattno 7}} {TARGETENTRY:resdom {RESDOM :resno 8 :restype
^^^^^^^^^^^^^^^^^^^

I suppose that this is just a cut-and-paste issue but you should double
check it.

> peufeu=> SELECT pdb.datname FROM pg_database pdb, pg_user pu;
> ERREUR: unexpected right parenthesis

> Which is interesting, as there is no parenthesis in this query ;)

Yes, the complaint is about something wrong with the stored form of the
view rule for pg_user.

> I'm totally perplexed. Have you got any idea ?

I'm starting to wonder about compiler bugs breaking the code in read.c
and readfuncs.c. If so, it's entirely likely that initdb wouldn't fix
it (and more than likely would fail halfway through :-()

If you want, you can try building with --enable-debug and getting a gdb
traceback from the call to errfinish(). That would at least give us
some clue where in the rule text it's getting confused.

regards, tom lane

> If you want, you can try building with --enable-debug and getting a gdb
> traceback from the call to errfinish(). That would at least give us
> some clue where in the rule text it's getting confused.

Is this :

./configure --enable-debug

?

I have no idea what to type in gbd to get the trace, though....

PFC <lists(at)boutiquenumerique(dot)com> writes:
> I have no idea what to type in gbd to get the trace, though....

What I usually do is
- start a psql session
- in another window, find out the PID of the backend attached
to the psql session, and do
$ gdb /path/to/postgres backend_PID
...
gdb> b errfinish
gdb> cont
- go back to psql session and issue problem command
- when gdb stops execution, do
gdb> bt
... useful printout is here ...
gdb> quit
sure you want to exit? y

regards, tom lane

Sometimes life has an irony of itself.
Today I modified some of my gentoo USE flags for something totally
unrelated to postgres.
Tonight I built postgres in debug mode : the offending query worked.
I thught "hm."
I rebuilt it without debug, and it still works.
I don't know what made it NOT to work before, I sure didn't hallucinate.
It must be some obscure incompatibility deep inside the Gentoo package and
build manager...
phppgadmin works, and pgadmin doesn't, telling me the "datapath" column
doesn't exist, no idea what this means. I'm going to look into it.

Thanks for your help and sorry about bothering you !

On Mon, 11 Apr 2005 18:42:29 +0200, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> PFC <lists(at)boutiquenumerique(dot)com> writes:
>> I have no idea what to type in gbd to get the trace, though....
>
> What I usually do is
> - start a psql session
> - in another window, find out the PID of the backend attached
> to the psql session, and do
> $ gdb /path/to/postgres backend_PID
> ...
> gdb> b errfinish
> gdb> cont
> - go back to psql session and issue problem command
> - when gdb stops execution, do
> gdb> bt
> ... useful printout is here ...
> gdb> quit
> sure you want to exit? y
>
> regards, tom lane
>

pgadmin3 does this (from pg's log with level set at debug5):

INSTRUCTION : SELECT db.oid, datname, datpath, datallowconn, datconfig,
datacl, pg_encoding_to_char(encoding) AS serverencoding,
pg_get_userbyid(datdba) AS datowner,has_database_privilege(db.oid,
'CREATE') as cancreate
FROM pg_database db
ORDER BY datname
ERREUR: 42703: la colonne <<datpath>> n'existe pas
EMPLACEMENT : transformColumnRef, parse_expr.c:1099

Do you know if this is normal, should this column exist, is it a problem
with pgadmin ?
Thanks !

On Mon, 11 Apr 2005 18:42:29 +0200, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> PFC <lists(at)boutiquenumerique(dot)com> writes:
>> I have no idea what to type in gbd to get the trace, though....
>
> What I usually do is
> - start a psql session
> - in another window, find out the PID of the backend attached
> to the psql session, and do
> $ gdb /path/to/postgres backend_PID
> ...
> gdb> b errfinish
> gdb> cont
> - go back to psql session and issue problem command
> - when gdb stops execution, do
> gdb> bt
> ... useful printout is here ...
> gdb> quit
> sure you want to exit? y
>
> regards, tom lane
>

PFC <lists(at)boutiquenumerique(dot)com> writes:
> ERREUR: 42703: la colonne <<datpath>> n'existe pas
> EMPLACEMENT : transformColumnRef, parse_expr.c:1099

> Do you know if this is normal, should this column exist, is it a problem
> with pgadmin ?

You need a newer pgadmin --- pg_database.datpath went away in 8.0.

regards, tom lane

> You need a newer pgadmin --- pg_database.datpath went away in 8.0.

I'm installing the new version. Thanks.