Postgres Pro
Downloads
Home   >   mailing lists

Thread: adddepend and partial indexes

adddepend and partial indexes

From
"SZŰCS Gábor"
Date:
Dear Fellows,

Couldn't find a list on adddepend, nor this problem via Google nor search at
postgresql.org, so here it goes.

Adddepend seems to have problem with partial indexes. Below is an example.
I can see the bugous regular subst in the perl script and I'd probably be
able to correct it, but I don't know how to use ALTER TABLE to add a partial
UNIQUE constraint (or shouldn't I add it at all?)

Also, I can't see the meaning of the second regular subst (as shown below).

I'd appreciate any source of info or explanation on this.

G.
------------------------------- 7.3.2
adddepend -------------------------------
------------------------------- Func:
findUniqueConstraints -------------------
# ...
    # Fetch vars
    my $constraint_name = $row->{'index_name'};
    my $table = $row->{'table_name'};
    my $columns = $row->{'constraint_definition'};

    # Extract the columns from the index definition
    $columns =~ s|.*\(([^\)]+)\).*|$1|g;
    $columns =~ s|([^\s]+)[^\s]+_ops|$1|g;

    my $upsql = qq{
DROP INDEX $constraint_name RESTRICT;
ALTER TABLE $table ADD CONSTRAINT $constraint_name UNIQUE ($columns);
    };
# ...

----------------------------------------------------------------------------
--
----------------------- result of SELECT to fetch rows from,
formatted -------
-[ RECORD
1 ]---------+-------------------------------------------------------------in
dex_name            | pakolas_cikktetel_helyre
table_name            | pakolas_cikktetel
constraint_definition | CREATE UNIQUE INDEX pakolas_cikktetel_helyre ON
                        pakolas_cikktetel USING btree
                        (pakolas, cikk, minoseg, helyre)
                        WHERE ((helyre IS NOT NULL) AND (helyrol IS NULL))
-[ RECORD
2 ]---------+-------------------------------------------------------------in
dex_name            | pakolas_cikktetel_helyrol
table_name            | pakolas_cikktetel
constraint_definition | CREATE UNIQUE INDEX pakolas_cikktetel_helyrol ON
                        pakolas_cikktetel USING btree
                        (pakolas, cikk, minoseg, helyrol)
                        WHERE ((helyrol IS NOT NULL) AND (helyre IS NULL))


----------------------------------------------------------------------------
--
----------------------- error message in
psql.log ----------------------------
2003-06-16 20:32:18 [6946]   ERROR:  parser: parse error at or near "IS" at
character 134
2003-06-16 20:32:18 [6946]   LOG:  statement:
DROP INDEX pakolas_cikktetel_helyre RESTRICT;
ALTER TABLE pakolas_cikktetel ADD CONSTRAINT pakolas_cikktetel_helyre UNIQUE
(helyrol IS NULL);

2003-06-16 20:32:18 [6946]   ERROR:  parser: parse error at or near "IS" at
character 135
2003-06-16 20:32:18 [6946]   LOG:  statement:
DROP INDEX pakolas_cikktetel_helyrol RESTRICT;
ALTER TABLE pakolas_cikktetel ADD CONSTRAINT pakolas_cikktetel_helyrol
UNIQUE (helyre IS NULL);

Re: adddepend and partial indexes

From
Stephan Szabo
Date:
On Tue, 17 Jun 2003, [iso-8859-2] SZ�CS G�bor wrote:

> Dear Fellows,
>
> Couldn't find a list on adddepend, nor this problem via Google nor search at
> postgresql.org, so here it goes.
>
> Adddepend seems to have problem with partial indexes. Below is an example.
> I can see the bugous regular subst in the perl script and I'd probably be
> able to correct it, but I don't know how to use ALTER TABLE to add a partial
> UNIQUE constraint (or shouldn't I add it at all?)

AFAIK there's no such thing as a partial unique constraint so the index
should probably just be left alone.

> Also, I can't see the meaning of the second regular subst (as shown below).

Hmm, I'd have guessed it was to take off index opclass information, but it
doesn't seem to do that (and doing so seems incorrect if its an index
using a non-default opclass).

> ------------------------------- 7.3.2
> adddepend -------------------------------
> ------------------------------- Func:
> findUniqueConstraints -------------------
> # ...
>     # Fetch vars
>     my $constraint_name = $row->{'index_name'};
>     my $table = $row->{'table_name'};
>     my $columns = $row->{'constraint_definition'};
>
>     # Extract the columns from the index definition
>     $columns =~ s|.*\(([^\)]+)\).*|$1|g;
>     $columns =~ s|([^\s]+)[^\s]+_ops|$1|g;

Re: adddepend and partial indexes

From
Bruno Wolff III
Date:
On Tue, Jun 17, 2003 at 07:48:06 -0700,
  Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote:
>
> AFAIK there's no such thing as a partial unique constraint so the index
> should probably just be left alone.

bruno=> create table test (col int);
CREATE TABLE
bruno=> create unique index test1 on test(col) where col < 100;
CREATE INDEX

Re: adddepend and partial indexes

From
Stephan Szabo
Date:
On Tue, 17 Jun 2003, Bruno Wolff III wrote:

> On Tue, Jun 17, 2003 at 07:48:06 -0700,
>   Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote:
> >
> > AFAIK there's no such thing as a partial unique constraint so the index
> > should probably just be left alone.
>
> bruno=> create table test (col int);
> CREATE TABLE
> bruno=> create unique index test1 on test(col) where col < 100;
> CREATE INDEX

That's a partial unique index, not a partial unique constraint. ;)
Which I believe is the point, there's no ALTER TABLE ADD CONSTRAINT that
presumably makes such an index.

Re: adddepend and partial indexes

From
Tom Lane
Date:
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> AFAIK there's no such thing as a partial unique constraint so the index
> should probably just be left alone.

Yeah, that seems like a bug in adddepend --- it shouldn't try to
transform *all* unique indexes into unique constraints, only those
that can be expressed in the constraint syntax.  So, no partial
indexes, no functional indexes, no non-btree indexes, and no
non-default opclasses.  Rod, do you have time to fix that?

            regards, tom lane

Re: adddepend and partial indexes

From
SZUCS Gábor
Date:
Dear Bruno,

I have no prob with "create index". I have prob with "alter table". A
figured that adddepend would do the index via "alter table" to ensure
dependencies.


Dear Stephan,

thanks for your opinion. I think I can ignore adddepend's parse errors on
this; however:

Is there a bug-report or fix-report or CVS place for adddepend? I'd gladly
do a quick-fix for this and the opclass thing (if someone helps with that
one).

G.
------------------------------- cut here -------------------------------
----- Original Message -----
From: "Bruno Wolff III" <bruno@wolff.to>
Sent: Tuesday, June 17, 2003 5:04 PM


> On Tue, Jun 17, 2003 at 07:48:06 -0700,
>   Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote:
> >
> > AFAIK there's no such thing as a partial unique constraint so the index
> > should probably just be left alone.
>
> bruno=> create table test (col int);
> CREATE TABLE
> bruno=> create unique index test1 on test(col) where col < 100;
> CREATE INDEX

Re: adddepend and partial indexes

From
Bruno Wolff III
Date:
On Tue, Jun 17, 2003 at 08:27:33 -0700,
  Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote:
>
> That's a partial unique index, not a partial unique constraint. ;)
> Which I believe is the point, there's no ALTER TABLE ADD CONSTRAINT that
> presumably makes such an index.

OK. That makes sense.