Thread: problem with casts dump/restore

I just noticed that pg_dump does not seem to be exporting at least one
of my user defined casts...In particular, this one:

CREATE CAST (xid AS oid)
  WITHOUT FUNCTION;

On a sort of related note, I noticed that pgAdmin 2.0 generates this
code in the SQL window for the above cast...

CREATE CAST (xid AS oid)
  WITHOUT FUNCTION
  AS EXPLICIT;

'create cast' does not have an 'explicit' cause...although IMHO maybe it
should (there is an 'e' in pg_cast.castcontext).


Merlin

"Merlin Moncure" <merlin.moncure@rcsonline.com> writes:
> I just noticed that pg_dump does not seem to be exporting at least one
> of my user defined casts...In particular, this one:

> CREATE CAST (xid AS oid)
>   WITHOUT FUNCTION;

This is per design, more or less:
   /*    * As per discussion we dump casts if one or more of the underlying    * objects (the conversion function and
thetwo data types) are not    * builtin AND if all of the non-builtin objects namespaces are    * included in the dump.
Builtinmeaning, the namespace name does not    * start with "pg_".    */
 

(The discussion in question is from late Sept 2003.)

The problem is basically that there's no way to detect that this isn't a
built-in cast.

In 7.3 and later there is another way to attack that question, which is
to look to see if there's a "pin" dependency in pg_depend for the cast.
Kinda ugly but it might do.
        regards, tom lane

> "Merlin Moncure" <merlin.moncure@rcsonline.com> writes:
> > I just noticed that pg_dump does not seem to be exporting at least
one
> > of my user defined casts...In particular, this one:
>
> > CREATE CAST (xid AS oid)
> >   WITHOUT FUNCTION;
>
> This is per design, more or less:
>
>     /*
>      * As per discussion we dump casts if one or more of the
underlying
>      * objects (the conversion function and the two data types) are
not
>      * builtin AND if all of the non-builtin objects namespaces are
>      * included in the dump. Builtin meaning, the namespace name does
not
>      * start with "pg_".
>      */
>
> (The discussion in question is from late Sept 2003.)
>
> The problem is basically that there's no way to detect that this isn't
a
> built-in cast.
>
> In 7.3 and later there is another way to attack that question, which
is
> to look to see if there's a "pin" dependency in pg_depend for the
cast.
> Kinda ugly but it might do.

Hmm...well, that makes perfect sense.  I suppose it's easy enough to
work around the problem by splitting the cast in two, and that's not
necessarily bad style, IMO.

The reason why I did that to begin with was to be able to do some
in-query processing on a xid.  Is it intentional that oid has a built in
cast to integer and xid does not?

Merlin

"Merlin Moncure" <merlin.moncure@rcsonline.com> writes:
> The reason why I did that to begin with was to be able to do some
> in-query processing on a xid.  Is it intentional that oid has a built in
> cast to integer and xid does not?

I'm not sure how intentional it is, but doing integer arithmetic on XIDs
seems pretty fraught with peril to me.  The comparison semantics on XIDs
are quite unlike normal integer comparisons.
        regards, tom lane

> "Merlin Moncure" <merlin.moncure@rcsonline.com> writes:
> > The reason why I did that to begin with was to be able to do some
> > in-query processing on a xid.  Is it intentional that oid has a
built in
> > cast to integer and xid does not?
>
> I'm not sure how intentional it is, but doing integer arithmetic on
XIDs
> seems pretty fraught with peril to me.  The comparison semantics on
XIDs
> are quite unlike normal integer comparisons.
>
>             regards, tom lane

Right.  Well, my reasons for doing this were pretty unusual.  I
'borrowed' the transaction column of pg_lock_status() so that it
returned the block# from the locktag.  Since this value for user locks
is application defined, it's natural to do something with it, bit
shifting it in this case.

I guess maybe this whole approach is a bad idea...maybe the best way to
return user lock information would be to make a separate function,
pg_user_lock_status() or something like that.  Anyways, thanks for
taking the time to look at it.

Merlin

I have not had time to install RC4 / RC5 yet, but have anyone tried this:

Createdb -Eunicode test
Psql test -c 'create table foo (bar text);

Then connect using libpq, and in your software application (C) pqexec something like:

'set client_encoding=ascii'
Insert into foo(bar) values('some text with accented characters');



Pg_dump was never able to dump that in a format that it was possible to restore with psql.
I usually had to manually edit the dump, and issue set client_encoding=ascii; set client_encoding=unicode; before and
afterthe entries that was inserted with ascii encoding. 

We're talking a table with millions of entries where only a few had been inserted using ascii encoding.

If someone could test it before release it would be good, unfortunately I will not have the time to do so myself.

Kind Regards,

John