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: https://github.com/kputnam/piggly/issues/19#issuecomment-1794004

[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!