pg_catalog.int does not exist

[lcoldiron]

/piggly/piggly/lib/piggly/installer.rb:50:in `exec': ERROR: type "pg_catalog.int" does not exist (PGError)

Error installing traced procedure my_schema.my_proc from /piggly/piggly/3530/piggly/cache/Dumper/6bd1ffffba0c2638792631166a10e1df.plpgsql
from /piggly/piggly/lib/piggly/installer.rb:50:in trace' from /piggly/piggly/lib/piggly/installer.rb:16:ininstall'
from /piggly/piggly/lib/piggly/installer.rb:14:in each' from /piggly/piggly/lib/piggly/installer.rb:14:ininstall'
from /piggly/piggly/lib/piggly/command/trace.rb:55:in install' from /piggly/piggly/lib/piggly/command/trace.rb:30:inmain'
from /piggly/piggly/lib/piggly/command/base.rb:15:in `main'
from /piggly/piggly/bin/piggly:8
restoring 576 procedures

snippet from index.yml

name: !ruby/object:Piggly::Dumper::QualifiedName
names:
- my_schema
- my_proc
oid: "48986"
secdef: false
setof: false
strict: true
type: !ruby/object:Piggly::Dumper::QualifiedName
names:
- pg_catalog
- int
volatility: volatile

• !ruby/object:Piggly::Dumper::SkeletonProcedure
  arg_modes: []

I think postgres is complaining that it doesn't like the schema qualified datatype.

[kputnam]

This could be a PostgreSQL version issue. Do the examples also fail with examples/run-tests?

[lcoldiron]

I'll check when I get back to my desk. I think I have a fix that will work. I can send you the code since I can't upload it at work.

Sent from my HTC

----- Reply message -----
From: "kputnam" reply@reply.github.com
Date: Fri, Aug 12, 2011 12:13 pm
Subject: [piggly] pg_catalog.int does not exist (#19)
To: luke@cedarstump.com

This could be a PostgreSQL version issue. Do the examples also fail with examples/run-tests?

Reply to this email directly or view it on GitHub:
#19 (comment)

[lcoldiron]

I ran the example/run-tests and don't see the issue but I think it might be because none of the example procs have a return type of int.

CREATE OR REPLACE FUNCTION public.my_func()
RETURNS "pg_catalog"."int" AS
$BODY$
BEGIN
END;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER;

I tried the following in Postgres 8.3 and 8.4 and it doesn't seem to work.

My fix for this is a little bit of a hack but in lib/piggly/dumper/procedure.rb line:232
Before: q(hash["typens"], shorten(hash["type"])),
After: hash["typens"] == "pg_catalog" ? q(hash["type"]) : q(hash["typens"], shorten(hash["type"])),

This seems to work for the situation where you need to schema qualify for row types and when you don't for primitive types.

[kputnam]

Yeah, that's basically what I had in mind for a fix. Might as well get rid of references to the "pg_catalog" schema altogether... the initialize method for QualifiedName should probably be

def initialize(name, *names)
  @names =
    if name == "pg_catalog" and not names.empty?
      names
    else
      [name, *names]
    end
end

Feel free to commit this and push when you get a chance.

[kputnam]

Still noticing a problem -- QualifiedName#quote doesn't suppress "pg_catalog".

[kputnam]

Finally took the 30 minutes to sit down and figure this out. Should be OK now!