[HACKERS] fetch_search_path() and elog.c

[Ferruccio Zamuner]

Hi,

I need to have in the log_line_prefix the search_path where the query 
has run.
So last week I've started to read elog.c and I was thinking about a 
small patch there using a new %S option.


First I've introduced a small code:
 case 'S':
   {
   List *search_path = fetch_search_path(false);
   if (search_path != NIL) {
   ListCell   *l;
   foreach(l, search_path)
  {
  char   *nspname;

  nspname = get_namespace_name(lfirst_oid(l));
  if (nspname)/* watch out for 
deleted namespace */

  {
   appendStringInfo(buf, %s , nspname);
   pfree(nspname);
 }
 }
   list_free(search_path);
   }
   break;
   }

but in this way postgres was starting with a core dump writing on log.

Then I've add some code, and now it starts but it kills the postgres 
process as soon as a query has sent to log:


$ diff -u elog.c elog_new.c
--- elog.c  Sat Mar 12 02:55:15 2005
+++ elog_new.c  Sun Jul 10 10:16:35 2005
@@ -67,7 +67,8 @@
#include tcop/tcopprot.h
#include utils/memutils.h
#include utils/guc.h
-
+#include catalog/namespace.h
+#include catalog/pg_type.h

/* Global variables */
ErrorContextCallback *error_context_stack = NULL;
@@ -1444,6 +1445,32 @@
   case '%':
   appendStringInfoChar(buf, '%');
   break;
+   /* */
+   case 'S':
+   /* estrae il search_path */
+ if (MyProcPort  (MyProcPort-commandTag != 
NULL)) {

+   char *cmd=MyProcPort-commandTag;
+   if ((strcasecmp(cmd,SELECT)== 0) || 
(strcasecmp(cmd,INSERT)== 0) || (strcasecmp(cmd,UPDATE)== 0) || 
(strcasecmp(cmd,DELETE)== 0)) {
+
+   }

+ }
+ break;
+List *search_path = fetch_search_path(false);
+ if (search_path != NIL) {
+   ListCell   *l;
+   foreach(l, search_path)
+ {
+   char   *nspname;
+
+   nspname = 
get_namespace_name(lfirst_oid(l));
+   if (nspname)/* 
watch out for deleted namespace */

+ {
+   appendStringInfo(buf, %s , 
nspname);

+   pfree(nspname);
+ }
+ }
+   list_free(search_path);
+ }
   default:
   /* format error - ignore it */
   break;

And here there is the client output:

bash-2.05a$ psql prova -U pgsql
Welcome to psql 8.0.3, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
  \h for help with SQL commands
  \? for help with psql commands
  \g or terminate with semicolon to execute query
  \q to quit

prova=# select * from prova;
server closed the connection unexpectedly
   This probably means the server terminated abnormally
   before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!

Where can I start to understand which checks I've missed and how to gain 
the output I need?



Thank you in advance,\ferz
---
NonSoLoSoft - http://www.nonsolosoft.com/

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [HACKERS] fetch_search_path() and elog.c

[Ferruccio Zamuner]

Tom Lane wrote:


Ferruccio Zamuner [EMAIL PROTECTED] writes:
 

I need to have in the log_line_prefix the search_path where the query 
has run.
So last week I've started to read elog.c and I was thinking about a 
small patch there using a new %S option.
   



There's no chance of that code working when not inside a transaction;
which means that in most of the scenarios where you really want a log
entry to be made, it will fail.

regards, tom lane
 


Now I've found a simpler solution:

case 'S':
   /* estrae il search_path */
 if (namespace_search_path != NULL)
   appendStringInfo(buf, %s , namespace_search_path);
 break;

It seems to work.

But I would like to understand why previous code was wrong.

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

[HACKERS] Weird NOT IN condition in SELECT (PostgreSQL 7.4.3 and 7.4.2 tested)

[Ferruccio Zamuner]

Hi,
I've following select, and I expect to receive a single record as result from it:
select c.id
  from copie as c
 where c.enum=46857 and
c.condizio_prestito = 'A' and
c.id not in (select id_copia from testi_fermi_prenotati) and
c.id not in (select id_copia from prestiti);
but it doesn't give me any records at all.
Following there is table descriptions:
CREATE TABLE copie (
id serial NOT NULL primary key,
enum integer,
id_biblioteca integer NOT NULL,
serie text,
collocazione text,
note text,
condizio_prestito character(1) DEFAULT 'A'::bpchar,
ctime date DEFAULT now(),
mtime date,
inventario integer
);
CREATE TABLE prestiti (
id serial NOT NULL,
inizio date DEFAULT now(),
id_libro text,
id_utente text,
fine date,
scadenza date NOT NULL,
stato smallint DEFAULT 1,
id_copia integer references copie(id)
);
CREATE TABLE testi_fermi_prenotati (
id_copia integer NOT NULL references copie(id),
id_prenotazione integer NOT NULL,
fermato_il timestamp without time zone DEFAULT now() NOT NULL,
scadenza timestamp without time zone,
stato character(1) DEFAULT 'a'::bpchar
);
and some usefull query results:
prove= select * from copie where enum=46857;
  id   | enum  | id_biblioteca | serie | collocazione | note | 
condizio_prestito |   ctime| mtime | inventario
---+---+---+---+--+--+---++---+
 37163 | 46857 | 1 |   | F RRN MAY|  | A 
  | 2004-05-03 |   |  41576
(1 row)

(SEE THE FOLLOWING QUERY AND RESULT)
prove= select * from prestiti where id_copia=37163;
 id | inizio | id_libro | id_utente | fine | scadenza | stato | id_copia
++--+---+--+--+---+--
(0 rows)
prove= select * from testi_fermi_prenotati where id_copia=37163;
 id_copia | id_prenotazione | fermato_il | scadenza | stato
--+-++--+---
(0 rows)
prove= select c.id
  from copie as c
 where c.enum=46857 and
c.condizio_prestito = 'A' and
c.id not in (select id_copia from testi_fermi_prenotati) and
c.id not in (select id_copia from prestiti);
id

(0 rows)
prove= select c.id
  from copie as c
 where c.enum=46857 and
c.condizio_prestito = 'A' and
c.id not in (select id_copia from testi_fermi_prenotati);
id
---
 37163
(1 row)
prove= select c.id
  from copie as c
 where c.enum=46857 and
c.condizio_prestito = 'A' and
c.id not in (select id_copia from prestiti);
id

(0 rows)
prove= explain analyze select c.id
  from copie as c
 where c.enum=46857 and
c.condizio_prestito = 'A' and
c.id not in (select id_copia from testi_fermi_prenotati) and
c.id not in (select id_copia from prestiti);
 QUERY PLAN 

-
 Seq Scan on copie c  (cost=0.00..14587.17 rows=1 width=4) (actual 
time=15.82..15.82 rows=0 loops=1)
   Filter: ((enum = 46857) AND (condizio_prestito = 'A'::bpchar) AND (subplan) 
AND (subplan))
   SubPlan
 -  Seq Scan on testi_fermi_prenotati  (cost=0.00..0.00 rows=1 width=4) 
(actual time=0.00..0.00 rows=0 loops=1)
 -  Seq Scan on prestiti  (cost=0.00..23.41 rows=1241 width=4) (actual 
time=0.01..10.21 rows=1241 loops=1)
 Total runtime: 15.95 msec
(6 rows)

prove= explain select c.id
  from copie as c
 where c.enum=46857 and
	c.condizio_prestito = 'A' and
c.id not in (select id_copia from testi_fermi_prenotati) and
	c.id not in (select id_copia from prestiti);
   QUERY PLAN 

--
 Seq Scan on copie c  (cost=0.00..14587.17 rows=1 width=4)
   Filter: ((enum = 46857) AND (condizio_prestito = 'A'::bpchar) AND (subplan) 
AND (subplan))
   SubPlan
 -  Seq Scan on testi_fermi_prenotati  (cost=0.00..0.00 rows=1 width=4)
 -  Seq Scan on prestiti  (cost=0.00..23.41 rows=1241 width=4)
(5 rows)

If you want to play with these data:
http://diff.homeunix.net/anomalia.sql.gz
then
gzip -d anomalias.sql
createdb anydb
psql -f anomalia.sql anydb
Best wishes,  \ferz
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly

Re: [HACKERS] Weird NOT IN condition in SELECT (PostgreSQL

[Ferruccio Zamuner]

Richard Huxton said:
 Ferruccio Zamuner wrote:
 Hi,

 I've following select, and I expect to receive a single record as result
 from it:

 select c.id
   from copie as c
  where c.enum=46857 and
 c.condizio_prestito = 'A' and
 c.id not in (select id_copia from testi_fermi_prenotati) and
 c.id not in (select id_copia from prestiti);

 but it doesn't give me any records at all.

 CREATE TABLE prestiti (
 id_copia integer references copie(id)

 CREATE TABLE testi_fermi_prenotati (
 id_copia integer NOT NULL references copie(id),

 Are you sure you don't have any null values in prestiti.id_copia?

Yes, there is a null value and it has not to be there.
Thank you.

Bye,\ferz

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

[HACKERS] Data integrity and sanity check

[Ferruccio Zamuner]

Hi,

someone asks me about an utility to check any PostgreSQL database
data to be sure that:
1) there is not any page corrupted 
   (by a memory fault or a damaged disk)
2) re-check any constraint inserted into the database

I really don't know if PostgreSQL itself has any crc check on
its pages. Please, there is anyone able to confirm such function?

I've understood that PostgreSQL trust the operating system for
doing its work, but I don't know if there is any operating system
able to give warranty the memory sanity before allocation, during 
the memory use. 

According to me, if the database is well-designed it's not 
possible to find constraint violation on data already inserted
and accepted from the SQL engine. 
Am I in fault for this sentence?

 
Thank you in advance for any reply.


Best regards,  \fer

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [HACKERS] PRIMARY KEY and INHERITANCE

[Ferruccio Zamuner]

   From: Horst Herb [EMAIL PROTECTED]
   Date: Mon, 1 Jan 2001 10:09:52 +1100

 create table a (
id serial primary key,
something text
 );

 create table b (
morething text
 ) inherits (a);

 create table c (
trouble int references b;
 );

 NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
 check(s) ERROR:  PRIMARY KEY for referenced table "b" not found


 How is possible to resolve this bug?
 How is possible to talk about a ORDBMS with this kind of error?

 It is not a bug, I would call it a missing feature. I had the same problem, 
 and somebody from this list helped me with a private email. I think this 
 should be included in the FAQ and general documentation.
 What happens is that the attribute "id" is inherited, but the index on "id" 
 is not. The workaround is:

 create unique index table_b_id on b(id);

 Then the index exists, and the foreign key can be referenced.

Thank you Horst,

I was very happy for this workaround: it could make me able to use classes
during the design and it could give me a power approach, but now:

\di
 List of relations
   Name| Type  | Owner 
---+---+---
 a_pkey| index | fer
 table_b_id| index | fer

# create table c (test int references b);
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
ERROR:  PRIMARY KEY for referenced table "b" not found

I've also tried to build b_pkey unique index:
 b_pkey| index | fer

The trouble still persists.


May someone give me another workaround for PostgreSQL 7.0.3?


Thank you in advance,   \fer