Re: [HACKERS] Bug in pg_get_constraintdef (for deferrable constraints)

2003-01-08 Thread Bruce Momjian 

OK, patch applied to HEAD and 7.3.X.  It does suppress options that are
already the default:  (patch attached)

That is:

test=> CREATE TABLE a1 (x int primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'a1_pkey'
for table 'a1'
CREATE TABLE

test=> CREATE TABLE a2 (y int references a1 (x));
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
CREATE TABLE

dumps out as:

ALTER TABLE ONLY a2
ADD CONSTRAINT "$1" FOREIGN KEY (y) REFERENCES a1(x) ON UPDATE NO
ACTION ON DELETE NO ACTION;

However, this:

test=> create table a1 (x int primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'a1_pkey'
for table 'a1'
CREATE TABLE
test=> create table a2 (y int references a1 (x) deferrable initially
deferred);
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
CREATE TABLE

dumps out as;

ALTER TABLE ONLY a2
ADD CONSTRAINT "$1" FOREIGN KEY (y) REFERENCES a1(x) ON UPDATE NO
ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED;

---

Stephan Szabo wrote:
> 
> On Wed, 1 Jan 2003, Bruce Momjian wrote:
> 
> > Tom Lane wrote:
> > > Bruce Momjian <[EMAIL PROTECTED]> writes:
> > > > I see the values being stored on constriant creation, but not being used
> > > > anywhere:
> > >
> > > I believe the values that actually get inspected at runtime are the
> > > tgdeferrable and tginitdeferred fields in pg_trigger.  The columns in
> > > pg_constraint are just copies of these.
> > >
> > > It is not real clear to me whether it should be allowed to alter the
> > > deferrability status of a foreign-key constraint --- is that in the spec?
> >
> > The big problem is that while pg_dump's dump_trigger() looks at
> > tginitdeferred and dumps accordingly, pg_get_constraintdef doesn't look
> > at tginitdeferred, and therefore doesn't record the requirement as part
> > of ALTER TABLE ADD CONSTRAINT.
> 
> pg_get_constraintdef should probably be looking at condeferrable
> and condeferred in the pg_constraint row it's looking at.  Maybe something
> like the attached.

Content-Description: 

[ Attachment, skipping... ]

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

Index: src/backend/utils/adt/ruleutils.c
===
RCS file: /cvsroot/pgsql-server/src/backend/utils/adt/ruleutils.c,v
retrieving revision 1.129
diff -c -c -r1.129 ruleutils.c
*** src/backend/utils/adt/ruleutils.c   14 Dec 2002 00:17:59 -  1.129
--- src/backend/utils/adt/ruleutils.c   8 Jan 2003 22:51:03 -
***
*** 688,693 
--- 688,698 
}
appendStringInfo(&buf, " ON DELETE %s", string);
  
+   if (conForm->condeferrable)
+   appendStringInfo(&buf, " DEFERRABLE");
+   if (conForm->condeferred)
+   appendStringInfo(&buf, " INITIALLY DEFERRED");
+ 
break;
}
  


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

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [HACKERS] Bug in pg_get_constraintdef (for deferrable constraints)

2003-01-02 Thread Bruce Momjian 

Your patch has been added to the PostgreSQL unapplied patches list at:

http://momjian.postgresql.org/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.  It also will be
backpatched.


---


Stephan Szabo wrote:
> 
> On Wed, 1 Jan 2003, Bruce Momjian wrote:
> 
> > Tom Lane wrote:
> > > Bruce Momjian <[EMAIL PROTECTED]> writes:
> > > > I see the values being stored on constriant creation, but not being used
> > > > anywhere:
> > >
> > > I believe the values that actually get inspected at runtime are the
> > > tgdeferrable and tginitdeferred fields in pg_trigger.  The columns in
> > > pg_constraint are just copies of these.
> > >
> > > It is not real clear to me whether it should be allowed to alter the
> > > deferrability status of a foreign-key constraint --- is that in the spec?
> >
> > The big problem is that while pg_dump's dump_trigger() looks at
> > tginitdeferred and dumps accordingly, pg_get_constraintdef doesn't look
> > at tginitdeferred, and therefore doesn't record the requirement as part
> > of ALTER TABLE ADD CONSTRAINT.
> 
> pg_get_constraintdef should probably be looking at condeferrable
> and condeferred in the pg_constraint row it's looking at.  Maybe something
> like the attached.

Content-Description: 

[ Attachment, skipping... ]

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: [HACKERS] Bug in pg_get_constraintdef (for deferrable

2003-01-02 Thread Tom Lane 
Rod Taylor <[EMAIL PROTECTED]> writes:
> I think I initially forgot those options, and Stephans patch seems to be
> everything required -- though the psql display is a little more
> cluttered.

Yeah, it is.  Could we improve that by having pg_get_constraintdef add
text only when the setting isn't the default?

regards, tom lane

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: [HACKERS] Bug in pg_get_constraintdef (for deferrable

2003-01-02 Thread Stephan Szabo 

On 2 Jan 2003, Rod Taylor wrote:

> I think I initially forgot those options, and Stephans patch seems to be
> everything required -- though the psql display is a little more
> cluttered.

IIRC, theoretically only initially immediate deferrable actually
needs to specify both clauses (initially deferred guarantees deferrable
and not deferrable doesn't need an initially at all).  It seemed like that
was getting too cute though for a quick patch.


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

Re: [HACKERS] Bug in pg_get_constraintdef (for deferrable

2003-01-02 Thread Rod Taylor 
I think I initially forgot those options, and Stephans patch seems to be
everything required -- though the psql display is a little more
cluttered.

> pg_get_constraintdef should probably be looking at condeferrable
> and condeferred in the pg_constraint row it's looking at.  Maybe something
> like the attached.

-- 
Rod Taylor <[EMAIL PROTECTED]>

PGP Key: http://www.rbt.ca/rbtpub.asc



signature.asc
Description: This is a digitally signed message part

Re: [HACKERS] Bug in pg_get_constraintdef (for deferrable constraints)

2003-01-01 Thread Stephan Szabo 

On Wed, 1 Jan 2003, Bruce Momjian wrote:

> Tom Lane wrote:
> > Bruce Momjian <[EMAIL PROTECTED]> writes:
> > > I see the values being stored on constriant creation, but not being used
> > > anywhere:
> >
> > I believe the values that actually get inspected at runtime are the
> > tgdeferrable and tginitdeferred fields in pg_trigger.  The columns in
> > pg_constraint are just copies of these.
> >
> > It is not real clear to me whether it should be allowed to alter the
> > deferrability status of a foreign-key constraint --- is that in the spec?
>
> The big problem is that while pg_dump's dump_trigger() looks at
> tginitdeferred and dumps accordingly, pg_get_constraintdef doesn't look
> at tginitdeferred, and therefore doesn't record the requirement as part
> of ALTER TABLE ADD CONSTRAINT.

pg_get_constraintdef should probably be looking at condeferrable
and condeferred in the pg_constraint row it's looking at.  Maybe something
like the attached.

*** pgsql/src/backend/utils/adt/ruleutils.c 2003-01-01 15:03:35.0 -0800
--- pgsql/src/backend/utils/adt/ruleutils.c.new 2003-01-01 15:02:32.0 -0800
***
*** 688,693 
--- 688,704 
}
appendStringInfo(&buf, " ON DELETE %s", string);
  
+   if (!conForm->condeferrable) {
+   appendStringInfo(&buf, " NOT");
+   }
+   appendStringInfo(&buf, " DEFERRABLE");
+   if (conForm->condeferred) {
+   appendStringInfo(&buf, " INITIALLY DEFERRED");
+   }
+   else {
+   appendStringInfo(&buf, " INITIALLY 
+IMMEDIATE");
+   }
+ 
break;
}
  


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] Bug in pg_get_constraintdef (for deferrable constraints)

2003-01-01 Thread Bruce Momjian 
Tom Lane wrote:
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > I see the values being stored on constriant creation, but not being used
> > anywhere:
> 
> I believe the values that actually get inspected at runtime are the
> tgdeferrable and tginitdeferred fields in pg_trigger.  The columns in
> pg_constraint are just copies of these.
> 
> It is not real clear to me whether it should be allowed to alter the
> deferrability status of a foreign-key constraint --- is that in the spec?

The big problem is that while pg_dump's dump_trigger() looks at
tginitdeferred and dumps accordingly, pg_get_constraintdef doesn't look
at tginitdeferred, and therefore doesn't record the requirement as part
of ALTER TABLE ADD CONSTRAINT.

Attached is a dump of the supplied example, showing that the outputs are the
same.   Looks like this is a must-fix for 7.3.2.


-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

--
-- PostgreSQL database dump
--

\connect - postgres

SET search_path = public, pg_catalog;

--
-- TOC entry 2 (OID 149751)
-- Name: prim; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE prim (
i integer NOT NULL
);


--
-- TOC entry 3 (OID 149755)
-- Name: for1; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE for1 (
j integer
);


--
-- TOC entry 4 (OID 149761)
-- Name: for2; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE for2 (
j integer
);


--
-- Data for TOC entry 6 (OID 149751)
-- Name: prim; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY prim (i) FROM stdin;
\.


--
-- Data for TOC entry 7 (OID 149755)
-- Name: for1; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY for1 (j) FROM stdin;
\.


--
-- Data for TOC entry 8 (OID 149761)
-- Name: for2; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY for2 (j) FROM stdin;
\.


--
-- TOC entry 5 (OID 149753)
-- Name: prim_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY prim
ADD CONSTRAINT prim_pkey PRIMARY KEY (i);


--
-- TOC entry 9 (OID 149757)
-- Name: $1; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY for1
ADD CONSTRAINT "$1" FOREIGN KEY (j) REFERENCES prim(i) ON UPDATE NO ACTION ON 
DELETE NO ACTION;


--
-- TOC entry 10 (OID 149763)
-- Name: $1; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY for2
ADD CONSTRAINT "$1" FOREIGN KEY (j) REFERENCES prim(i) ON UPDATE NO ACTION ON 
DELETE NO ACTION;




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

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [HACKERS] Bug in pg_get_constraintdef (for deferrable constraints)

2003-01-01 Thread Tom Lane 
Bruce Momjian <[EMAIL PROTECTED]> writes:
> I see the values being stored on constriant creation, but not being used
> anywhere:

I believe the values that actually get inspected at runtime are the
tgdeferrable and tginitdeferred fields in pg_trigger.  The columns in
pg_constraint are just copies of these.

It is not real clear to me whether it should be allowed to alter the
deferrability status of a foreign-key constraint --- is that in the spec?

regards, tom lane

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

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [HACKERS] Bug in pg_get_constraintdef (for deferrable constraints)

2003-01-01 Thread Bruce Momjian 

I can reproduce this failure here too.  I am actually quite confused
because:

1)  I know this deferrable stuff works or used to work
2)  I can't find relivant references to
condeferrable/Anum_pg_constraint_condeferrable and
condeferred/Anum_pg_constraint_condeferred in the code.

I see the values being stored on constriant creation, but not being used
anywhere:

$ rgrepc condefer
./backend/catalog/pg_constraint.c:  
values[Anum_pg_constraint_condeferrable - 1] = BoolGetDatum(isDeferrable);
./backend/catalog/pg_constraint.c:  values[Anum_pg_constraint_condeferred 
- 1] = BoolGetDatum(isDeferred);
./include/catalog/pg_constraint.h:  boolcondeferrable;  /* 
deferrable constraint? */
./include/catalog/pg_constraint.h:  boolcondeferred;/* 
deferred by default? */
./include/catalog/pg_constraint.h:#define Anum_pg_constraint_condeferrable 
4
./include/catalog/pg_constraint.h:#define Anum_pg_constraint_condeferred   
5

I am confused.

---

Magnus Hagander wrote:
> Postgresql 7.3.1 on Linux i386 - but from what I can see it is on all platforms
> 
> It seems pg_get_constraintdef does not remember the setting "DEFERRABLE" on a 
>constraint. This has the effect that it does not show up in psql \d commands, and it 
>is also *not* included in backups from pg_dump.
> 
> 
> Reproduce:
> CREATE TABLE foo.prim(i int PRIMARY KEY);
> CREATE TABLE foo.for1(j int REFERENCES foo.prim(i) NOT DEFERRABLE);
> CREATE TABLE foo.for2(j int REFERENCES foo.prim(i) DEFERRABLE);
> 
> "\d foo.for1" and "\d foo.for2" will then show the exact same definition of the 
>constraint:
>Table "foo.for2"
>  Column |  Type   | Modifiers
> +-+---
>  j  | integer |
> Foreign Key constraints: $1 FOREIGN KEY (j) REFERENCES foo.prim(i) ON UPDATE NO 
>ACTION ON DELETE NO ACTION
> 
> 
> 
> Seems to me like ruleutils.c at around line 600 is the place, and that is has no 
>concept of DEFERRABLE anywhere near that, but I'm not comfortable enough in there to 
>produce a patch myself... 
> 
> 
> //Magnus
> 
> 
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org
> 

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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