CREATE RULE fails with 'ERROR: SELECT rule's target list has too many entries'

Henrik Kuhn <henrik.kuhn@origenis.de> writes:

can somebody give me some insights why the creation of this rule fails
with 'ERROR: SELECT rule's target list has too many entries'?

Probably that history_relation has fewer than three columns? But if
you want something more than guessing, you'd have to provide more
context, such as the definitions of the underlying tables. Why are
you trying to execute this command anyway?

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 12/05/2012 04:07 PM, Tom Lane wrote:

Henrik Kuhn <henrik.kuhn@origenis.de> writes:

can somebody give me some insights why the creation of this rule fails
with 'ERROR: SELECT rule's target list has too many entries'?

Probably that history_relation has fewer than three columns? But if
you want something more than guessing, you'd have to provide more
context, such as the definitions of the underlying tables. Why are
you trying to execute this command anyway?

regards, tom lane

The history_relatio-DDL is quite simple:

CREATE TABLE history_relation (
id UUID NOT NULL DEFAULT uuid_generate_v4()
, relname name NOT NULL CHECK ( relname::regclass IS NOT NULL
) -- use regclass to validate

, PRIMARY KEY (id)
);

But are your really sure, that the error is thrown just because of there
are not more than 3 cols in history_relation? And then why is the error
text '... too many entries' ? It sounds more that the RULE parser can
not handle the join over four tables?

The purpose is:
I want to set up an own polymorphic foreign key reference validation
system. The key point to this is using uuid as prim. key over all tables
to reference to. Because the corresponding prim key names may be
different I need to query for its name to build the corresponding join
stmt. The TABLE history_relation is for keeping track of the foreign tables.

Regards,
Henrik

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Henrik Kuhn <henrik.kuhn@origenis.de> writes:

On 12/05/2012 04:07 PM, Tom Lane wrote:

Henrik Kuhn <henrik.kuhn@origenis.de> writes:

can somebody give me some insights why the creation of this rule fails
with 'ERROR: SELECT rule's target list has too many entries'?

Probably that history_relation has fewer than three columns? But if
you want something more than guessing, you'd have to provide more
context, such as the definitions of the underlying tables. Why are
you trying to execute this command anyway?

The history_relatio-DDL is quite simple:

CREATE TABLE history_relation (
id UUID NOT NULL DEFAULT uuid_generate_v4()
, relname name NOT NULL CHECK ( relname::regclass IS NOT NULL
) -- use regclass to validate

, PRIMARY KEY (id)
);

But are your really sure, that the error is thrown just because of there
are not more than 3 cols in history_relation?

Yes. I think you misunderstand what that command is for: it's going to
convert the table into a view, and as a safety check it is not allowed
for the resulting view to have a different column set than the table
did.

I think what you need to do is create the underlying table with some
other name, create history_relation as a plain view, and then perhaps
you want some INSTEAD OF triggers on the view to convert insertions
etc into updates of the underlying table.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 12/05/2012 04:58 PM, Tom Lane wrote:

Henrik Kuhn <henrik.kuhn@origenis.de> writes:

On 12/05/2012 04:07 PM, Tom Lane wrote:

Henrik Kuhn <henrik.kuhn@origenis.de> writes:

can somebody give me some insights why the creation of this rule fails
with 'ERROR: SELECT rule's target list has too many entries'?

Probably that history_relation has fewer than three columns? But if
you want something more than guessing, you'd have to provide more
context, such as the definitions of the underlying tables. Why are
you trying to execute this command anyway?

But are your really sure, that the error is thrown just because of there
are not more than 3 cols in history_relation?

Yes. I think you misunderstand what that command is for: it's going to
convert the table into a view, and as a safety check it is not allowed
for the resulting view to have a different column set than the table
did.

Tom,

OK, I've tried a the same cretae rule stmt but without the keyname col.
And it did not complain about to many entries.
But now the parser complains again with:

ERROR: could not convert table "history_relation" to a view because it
has triggers
HINT: In particular, the table cannot be involved in any foreign key
relationships.

The intention why I thought it is a nice feature to use a rule was, that
I do not need to create an extra view object upon the table.
I thought that the rule system does transform the simple select on table
history_relation to my desired multi table join
retrieving the extra col. keyname on the fly. Even the docs
(http://www.postgresql.org/docs/9.1/static/sql-createrule.html) say:
'... Thus, an ON SELECT rule effectively turns the table into a view,
whose visible contents are the rows returned by the rule's
SELECT command rather than whatever had been stored in the table (if
anything). ...'. No word upon that the returning
col. set/count must be the same

I think what you need to do is create the underlying table with some
other name, create history_relation as a plain view, and then perhaps
you want some INSTEAD OF triggers on the view to convert insertions
etc into updates of the underlying table.

regards, tom lane

Of course I had a view before, but a RULE seems to be much smarter in
the way that it masks the underlying table e.g. a select upon a table
behaves like a view,
but is not defined as one.

I'm switching back to the view based design.

Thanks for your help Tom.

Regads,
Henrik