Re: [GENERAL] [ANNOUNCE] PostgreSQL Core Team

2011-04-28 Thread Stéphane A. Schildknecht 
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Le 27/04/2011 20:48, Dave Page a écrit :
> I'm pleased to announce that effective immediately, Magnus Hagander
> will be joining the PostgreSQL Core Team.
> 
> Magnus has been a contributor to PostgreSQL for over 12 years, and
> played a major part in the development and ongoing maintenance of the
> native Windows port, quickly becoming a committer to help with his
> efforts. He's one of the project's webmasters and sysadmins and also
> contributes to related projects such as pgAdmin. In his spare time, he
> serves as President of the Board of PostgreSQL Europe.
> 
> Regards, Dave.
> 

Congratulations!


- -- 
Stéphane Schildknecht
Loxodata
Contact régional PostgreSQL

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAk25EkgACgkQA+REPKWGI0FJVQCgjfxVznkuhVZ2Ztc7lqWvf3fZ
C/oAoLyzCVRVuO5GiWmGljYeV6MdYLWu
=fsx5
-END PGP SIGNATURE-

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

[GENERAL] Trigger before delete does fire before, but delete doesn't not happen

2008-11-28 Thread Stéphane A. Schildknecht 
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hi,

I'm faced with something like a comprehension problem.
The exemple may be oversimplified, but, it seems same problem happens with 
updates.

To simplify, I have triggers on 2 tables (commande and commandeligne).

When deleting from table commande, a trigger fires to delete corresponding
entries in table commandeligne.

When deleting from table commandeligne a trigger fires to update sum of command
(column montant) in table commande.

I'm conscious that an "on delete cascade" on table commande would be really
better, conceptually and logically, but I would like to understand why I don't
get deletion of my tuple in table commande when firing triggers.

The test case I use is as follows :

#
drop table commande cascade;
drop table commandeligne;

CREATE TABLE commande
(
  id integer NOT NULL,
  montant real,
  CONSTRAINT id PRIMARY KEY (id)
)with oids;

CREATE TABLE commandeligne
(
  id_commande integer NOT NULL references commande (id)
- --  on delete cascade on update cascade
  ,
  montant real,
  id_produit integer NOT NULL,
  CONSTRAINT clef PRIMARY KEY (id_commande, id_produit)
)with oids;

CREATE OR REPLACE FUNCTION p_commande_bd() RETURNS "trigger" AS
$BODY$
BEGIN
-- RAISE NOTICE 'Table commandeligne : suppression de la ligne %', OLD.id;
DELETE FROM commandeligne WHERE id_commande = OLD.id;
-- RAISE NOTICE 'Table commandeligne : ligne % supprimée', OLD.id;
RETURN OLD;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

DROP TRIGGER  IF EXISTSp_commande_bd ON commande;
CREATE TRIGGER p_commande_bd before DELETE ON commande FOR Each row EXECUTE
PROCEDURE p_commande_bd();

CREATE OR REPLACE FUNCTION p_commandeligne_ad() RETURNS "trigger" AS
$BODY$
BEGIN
-- RAISE NOTICE 'Table commande : maj de la ligne %', OLD.id_commande;
UPDATE commande SET montant=montant-OLD.montant WHERE id = OLD.id_commande;
-- RAISE NOTICE 'Table commande : ligne % maj (%)', OLD.id_commande,
OLD.montant;
RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
DROP TRIGGER  IF EXISTSp_commandeligne_ad ON commandeligne;
CREATE TRIGGER p_commandeligne_ad  AFTER DELETE ON commandeligne FOR EACH ROW
EXECUTE PROCEDURE p_commandeligne_ad();


-  First step : Creating first command
insert into commande(id, montant) values(1,150);
insert into commandeligne(id_commande,id_produit, montant) values(1,1,100);
insert into commandeligne(id_commande,id_produit, montant) values(1,2,20);
insert into commandeligne(id_commande,id_produit, montant) values(1,3,30);

select oid,* from commande where id=1;
select oid,* from commandeligne where id_commande=1;

-  2nd step : Deletion of command 1
delete from commande where id=1;

select oid,* from commande where id=1;
select oid,* from commandeligne where id_commande=1;;



Command 1 is still there.

Thanks in advance.

Best regards,
- --
Stéphane Schildknecht
PostgreSQLFr - http://www.postgresql.fr
Dalibo - http://www.dalibo.com
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFJL9o+A+REPKWGI0ERAv0gAJ0XU41ZkrjTzm8AL5aG+NtO3m6IOACgsY08
JsTE7QefA+yh87P7V/Lel10=
=3WLn
-END PGP SIGNATURE-

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

Re: [GENERAL] Trigger before delete does fire before, but delete doesn't not happen

2008-11-30 Thread Stéphane A. Schildknecht 
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Adrian Klaver a écrit :
> On Friday 28 November 2008 3:47:10 am Stéphane A. Schildknecht wrote:
>> drop table commande cascade;
>> drop table commandeligne;
>>
>> CREATE TABLE commande
>> (
>>   id integer NOT NULL,
>>   montant real,
>>   CONSTRAINT id PRIMARY KEY (id)
>> )with oids;
>>
>> CREATE TABLE commandeligne
>> (
>>   id_commande integer NOT NULL references commande (id)
>> --  on delete cascade on update cascade
>>   ,
>>   montant real,
>>   id_produit integer NOT NULL,
>>   CONSTRAINT clef PRIMARY KEY (id_commande, id_produit)
>> )with oids;
>>
>> CREATE OR REPLACE FUNCTION p_commande_bd() RETURNS "trigger" AS
>> $BODY$
>> BEGIN
>> -- RAISE NOTICE 'Table commandeligne : suppression de la ligne %',
>> OLD.id; DELETE FROM commandeligne WHERE id_commande = OLD.id;
>> -- RAISE NOTICE 'Table commandeligne : ligne % supprimée', OLD.id;
>> RETURN OLD;
>> END;
>> $BODY$
>> LANGUAGE 'plpgsql' VOLATILE;
>>
>> DROP TRIGGER  IF EXISTSp_commande_bd ON commande;
>> CREATE TRIGGER p_commande_bd before DELETE ON commande FOR Each row EXECUTE
>> PROCEDURE p_commande_bd();
>>
>> CREATE OR REPLACE FUNCTION p_commandeligne_ad() RETURNS "trigger" AS
>> $BODY$
>> BEGIN
>> -- RAISE NOTICE 'Table commande : maj de la ligne %', OLD.id_commande;
>> UPDATE commande SET montant=montant-OLD.montant WHERE id =
>> OLD.id_commande; -- RAISE NOTICE 'Table commande : ligne % maj (%)',
>> OLD.id_commande, OLD.montant;
>> RETURN NEW;
>> END;
>> $BODY$
>> LANGUAGE 'plpgsql' VOLATILE;
>> DROP TRIGGER  IF EXISTSp_commandeligne_ad ON commandeligne;
>> CREATE TRIGGER p_commandeligne_ad  AFTER DELETE ON commandeligne FOR EACH
>> ROW EXECUTE PROCEDURE p_commandeligne_ad();
>>
>>
>>  First step : Creating first command
>> insert into commande(id, montant) values(1,150);
>> insert into commandeligne(id_commande,id_produit, montant) values(1,1,100);
>> insert into commandeligne(id_commande,id_produit, montant) values(1,2,20);
>> insert into commandeligne(id_commande,id_produit, montant) values(1,3,30);
>>
>> select oid,* from commande where id=1;
>> select oid,* from commandeligne where id_commande=1;
>>
>>  2nd step : Deletion of command 1
>> delete from commande where id=1;
> 
> When I run this test case I get:
> 
> test=#  2nd step : Deletion of command 1
> test=# delete from commande where id=1;
> ERROR:  update or delete on table "commande" violates foreign key 
> constraint "commandeligne_id_commande_fkey" on table "commandeligne"
> DETAIL:  Key (id)=(1) is still referenced from table "commandeligne".
> 
> The FK in  commandeligne (id_commande integer NOT NULL references commande 
> (id))  is preventing the trigger from completing.
> 

Here, I don't get that error.

Maybe you could try creating the commandeligne table like that :

CREATE TABLE commandeligne
(
  id_commande integer NOT NULL
  -- references commande (id)
  --  on delete cascade on update cascade
   ,
   montant real,
   id_produit integer NOT NULL,
   CONSTRAINT clef PRIMARY KEY (id_commande, id_produit)
)with oids;

I'm running PG 8.3.5 or 8.2.11, result is the same.

Regards,
- --
Stéphane Schildknecht
PostgreSQLFr - http://www.postgresql.fr
Dalibo - http://www.dalibo.com
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFJM4+zA+REPKWGI0ERAmeCAKCV5upN9r7174fzIQRLE6pajSc1tACg4pw0
SRmXwnN3huC4A6vteOo9CkE=
=mlSt
-END PGP SIGNATURE-

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

Re: [GENERAL] Trigger before delete does fire before, but delete doesn't not happen

2008-12-02 Thread Stéphane A. Schildknecht 
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Tom Lane a écrit :
> Adrian Klaver <[EMAIL PROTECTED]> writes:
>> Thanks for the explanation. Just so I am clear,the act of updating the row 
>> in p_commandeligne_ad creates a new tuple for the row with id of 1. This 
>> means the original statement "delete from commande where id=1" runs against 
>> a version of the row that no longer exists and becomes a no-op statement. 
>> This happens because the trigger was run as BEFORE and changed the row from 
>> under the original statement.
> 
> Right.
> 
>   regards, tom lane

Thanks for having helped me understand better why it couldn't be a logical way
of acting.

Best regards,
- --
Stéphane Schildknecht
PostgreSQLFr - http://www.postgresql.fr
Dalibo - http://www.dalibo.com
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFJNRmvA+REPKWGI0ERAkeIAKCMucAjbCS8tw5kXJqyCuNWS7pMjQCgu2MU
U4rECUpyOm5rqnr0FRmBT6o=
=b7ow
-END PGP SIGNATURE-

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

[GENERAL] Using xmin to identify last modified rows

2009-02-25 Thread Stéphane A. Schildknecht 
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hi,

Trying to identify last modified (updated or inserted) rows in a table, I
thought I could use xmin.

I tried is to get some lines sorted by xmin.

When doing it on a slonified database, I had no problem getting these lines.

But, trying the same query on a non slonified DB, I got an error, as there is
no ordering operator for xid.

I think that in the slon case, the query uses the implicit cast xid->xxid, and
then the operator to sort xxid.

What would be the best way to get last modified rows?

What I tried :
db=# select id_table, date_table, code_table from tb_table order by xmin desc
limit 10;
ERROR:  could not identify an ordering operator for type xid
ASTUCE : Use an explicit ordering operator or modify the query.

Thanks in advance.

Best regards,
- --
Stéphane Schildknecht
PostgreSQLFr - http://www.postgresql.fr
Dalibo - http://www.dalibo.com
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFJpTLoA+REPKWGI0ERAiCaAKCOSFQp/RtWFaLScwXLpqXQJKGzLgCgsNUn
jXCUCSBBXVP7WEIn/M0Pklc=
=PN5v
-END PGP SIGNATURE-

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

Re: [GENERAL] Using xmin to identify last modified rows

2009-02-25 Thread Stéphane A. Schildknecht 
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Gregory Stark a écrit :
> "Stéphane A. Schildknecht"  writes:
(...)
> 
> You could order by age(xmin) instead
> 
>> What would be the best way to get last modified rows?
> 
> I'm not sure using xmin is such a great idea really. It's handy for ad-hoc
> queries but there are all kinds of cases where it might not give you the
> results you expect. 
> 
> You probably want to put a timestamp column on your tables and manage the date
> you put in their according to a policy you control.
> 
> 

Gregory,

Thanks for the answer.

A timestamp would surely be a better idea. BTW, I don't have hand on the schema
yet and was just looking for a quick way to get some last modified rows.

Regards,
- --
Stéphane Schildknecht
PostgreSQLFr - http://www.postgresql.fr
Dalibo - http://www.dalibo.com
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFJpV6qA+REPKWGI0ERAhq/AJwNt845SDujYmFhe4aTqI30QBBC9gCg4vcH
edlSZti3KDtozJ82Od0nErQ=
=z1lm
-END PGP SIGNATURE-

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

[GENERAL] Preventing a user to use implicit casts

2009-07-01 Thread Stéphane A. Schildknecht 
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hi,

Is there a way to define some implicit casts so that only one specific database
user can use them, and they're not visible to others?

I'm facing the case where to have same behaviour as in 8.2 for this
"Non-character data types are no longer automatically cast to TEXT" 8.3
improvements, I used implicit casts proposed here:

http://wiki.postgresql.org/images/d/d1/Pg83-implicit-casts.sql

Problem yet is I can't use slony any more to replicate data, as it falls in
error with this kind of messages:

Jul  1 17:23:20 jck20 slon[6671]: [62-1] 2009-07-01 17:23:20 CEST FATAL
cleanupThread: "select "_gvr".cleanupEvent('10 minutes'::interval,
'false'::boolean); " - ERROR:
Jul  1 17:23:20 jck20 slon[6671]: [62-2]   operator is not unique: unknown ||
integer
Jul  1 17:23:20 jck20 slon[6671]: [62-3] LINE 1: SELECT  'PartInd_gvr_sl_log_'
||  $1  || '-node-' ||  $2
Jul  1 17:23:20 jck20 slon[6671]: [62-4]   ^
Jul  1 17:23:20 jck20 slon[6671]: [62-5] HINT:  Could not choose a best
candidate operator. You might need to add explicit type casts.
Jul  1 17:23:20 jck20 slon[6671]: [62-6] QUERY:  SELECT  'PartInd_gvr_sl_log_'
||  $1  || '-node-' ||  $2
Jul  1 17:23:20 jck20 slon[6671]: [62-7] CONTEXT:  PL/pgSQL function
"addpartiallogindices" line 28 at assignment
Jul  1 17:23:20 jck20 slon[6671]: [62-8] SQL statement "SELECT
"_gvr".addPartialLogIndices()"
Jul  1 17:23:20 jck20 slon[6671]: [62-9] PL/pgSQL function "logswitch_finish"
line 99 at PERFORM

I'm quit disappointed, as if I let slony replicate the application, still in a
process of migration to 8.3 correct behaviour can't be used, on the other hand
if I add implicit casts to allow application to work, I can't replicate with 
slony.

Thanks in advance for any help.

Regards,
- --
Stéphane Schildknecht
PostgreSQLFr - http://www.postgresql.fr

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFKS6OCA+REPKWGI0ERAqw1AJ9EPWbsDTp1K0glwBVW5+KCCbhcpgCggvh0
i8A4YmH3tg3lq7eK71IaaKI=
=wKwd
-END PGP SIGNATURE-

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

Re: [GENERAL] Preventing a user to use implicit casts

2009-07-01 Thread Stéphane A. Schildknecht 
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Scott Marlowe a écrit :
> On Wed, Jul 1, 2009 at 11:57 AM, "Stéphane A.
> Schildknecht" wrote:
>> Problem yet is I can't use slony any more to replicate data, as it falls in
> 
>> I'm quit disappointed, as if I let slony replicate the application, still in 
>> a
>> process of migration to 8.3 correct behaviour can't be used, on the other 
>> hand
>> if I add implicit casts to allow application to work, I can't replicate with 
>> slony.
> 
> So, is fixing the app an option?
> 

Definitely not... as it would last too long to be an option.
It surely is a long-term option. And it needs to be done, but not yet.

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFKS6a+A+REPKWGI0ERArdRAKCfsj/VCuY7zRNFVGkezW+TrUq0XwCgmD8Y
NCVJ3DWCT0PSGHtWGcUXg04=
=maRT
-END PGP SIGNATURE-

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

Re: [GENERAL] tgname munged

2010-08-04 Thread Stéphane A. Schildknecht 
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Le 04/08/2010 17:30, gnuo...@rcn.com a écrit :
> Can anyone explain why the following query (used by AQT to display its tree):
> 
> SELECT trig.tgname,
> tab.relname,
> case trig.tgenabled
> when '1' then 'yes'
> else 'no'
> end,
> trig.oid
> FROM pg_catalog.pg_trigger trig,
> pg_catalog.pg_class tab,
> pg_catalog.pg_namespace nam
> WHERE trig.tgrelid=tab.oid
> AND tab.relnamespace=nam.oid
> AND nam.nspname ='public' -- ?
> ORDER BY 1
> 
> produces tgname like (from memory) RIFKConstraint...
> while without the   ORDER BY  (and it doesn't matter how you identify the 
> column) tgname displays correctly (well, the same as what you see if you 
> SELECT * FROM pg_trigger).
> 

Hi,

Which version of PostgreSQL is it ?
I can't see any difference with PG 8.4.4.

Regards,

- -- 
Stéphane Schildknecht
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkxZjfsACgkQA+REPKWGI0HiMQCfWf54uFM3WMy3LsxQ+513J1il
ETsAoLD23uOijdlwOR7X4av+n9pmSi02
=eOIx
-END PGP SIGNATURE-

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

Re: [GENERAL] Relative performance of prefix and suffix string matching

2011-09-23 Thread Stéphane A. Schildknecht 
Le 23/09/2011 12:30, Alban Hertroys a écrit :
> On 23 September 2011 11:47, Andrew Rose  > wrote:
> 
> Basic Question: In text fields, is prefix matching significantly faster
> than suffix matching?
> 
> 
> It does depend on what type of index you use. BTrees split off text strings,
> from left to right, halving the number of records you need to scan at every
> branch. For a suffix match, that's exactly the wrong way around.
> Hash indexes probably don't fare any better.
> I don't know enough about GIST or GIN indexes to comment on their suitability
> for suffix matches, but presumably they're better at those.
> 
> I recall doing suffix matches used to be a problem in at least earlier 
> versions
> of Postgres, but it's quite possible that the query planner is smart enough to
> do the reverse match by itself nowadays (I doubt it, seeing that it would also
> need to reverse the way the index is organised).
>  
> 
> 2. Alternatively, I could store column 'rev_str' as a reversed version of
> column 'str' and have the client produce a reversed version of x on each
> query (call it r).  Then the client would issue...
> 
> SELECT * FROM tbl WHERE str LIKE 'x%' OR rev_str LIKE 'r%'
> 
> ...which would use prefix matches only instead of requiring suffix 
> matches.
>  Since I've seen this form used by others, I was wondering if it's
> necessary - i.e. if databases really do perform prefix matching faster?
> 
> 3. Is there a solution I'm unaware of with even better performance?
> 
>  
> You can create a functional index on the reverse of the string, that way
> omitting the need for an extra column (that needs updating as well).
> 
> CREATE INDEX tbl_str_rev ON tbl (reverse(str));
> SELECT * FROM tbl WHERE str LIKE 'x%' OR reverse(str) LIKE 'x%';
> 
> See: http://www.postgresql.org/docs/9.0/static/indexes-expressional.html

You can use the pg_trgm extension which lets you create gin or gist indexes on
your text field.

There is also wildspeed (see http://www.sai.msu.su/~megera/wiki/wildspeed).

Didn't try the latter solution, but the first one gives really great result for
searching partial strings.

a propos, there's one thing I'd like to know, is how to set the similarity
limit within pg_trgm on a server side (I'd like to have it settled to 0.2 for
every new session, for instance).

Regards,
-- 
Stéphane Schildknecht
http://www.loxodata.com
Contact régional PostgreSQL


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