Re: pg_class.relistemp

Hackers,

With regard to this change:

http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=5f7b58fad8f45c69bb67944779dce67e2f481995

I'm wondering if it would be possible to restore the relistemp column to pg_class, at least for backwards compatibility, so that apps that expected it can continue to work on both 9.0 and 9.1. Even if it's read-only somehow, and the same as `relpersistence <> 't'`.

I've run into this with pgTAP, and am having a hard time coming up with a simple code path to support both without a patch. It'd make life simpler if there was some sort of compatibility interface so that my code doesn't have to maintain two paths.

Thanks,

David

David E. Wheeler wrote:
> Hackers,
>
> With regard to this change:
>
> http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=5f7b58fad8f45c69bb67944779dce67e2f481995
>
> I'm wondering if it would be possible to restore the relistemp column
> to pg_class, at least for backwards compatibility, so that apps that
> expected it can continue to work on both 9.0 and 9.1. Even if it's
> read-only somehow, and the same as `relpersistence <> 't'`.

Uh, that is going to require an initdb, and it is unlinkely we are going
to need that this far into 9.1 beta. Also, we don't normally keep
system table columns around for backward compatibility because of the
confusion it can cause, e.g. which column do I look at?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On Jul 13, 2011, at 12:14 PM, Bruce Momjian wrote:

>> I'm wondering if it would be possible to restore the relistemp column
>> to pg_class, at least for backwards compatibility, so that apps that
>> expected it can continue to work on both 9.0 and 9.1. Even if it's
>> read-only somehow, and the same as `relpersistence <> 't'`.
>
> Uh, that is going to require an initdb, and it is unlinkely we are going
> to need that this far into 9.1 beta.

I was afraid of that.

> Also, we don't normally keep
> system table columns around for backward compatibility because of the
> confusion it can cause, e.g. which column do I look at?

The one that's documented.

Wasn't newsysviews supposed to deal with these sorts of issues? Why were they rejected?

Best,

David

David E. Wheeler wrote:
> On Jul 13, 2011, at 12:14 PM, Bruce Momjian wrote:
>
> >> I'm wondering if it would be possible to restore the relistemp column
> >> to pg_class, at least for backwards compatibility, so that apps that
> >> expected it can continue to work on both 9.0 and 9.1. Even if it's
> >> read-only somehow, and the same as `relpersistence <> 't'`.
> >
> > Uh, that is going to require an initdb, and it is unlinkely we are going
> > to need that this far into 9.1 beta.
>
> I was afraid of that.
>
> > Also, we don't normally keep
> > system table columns around for backward compatibility because of the
> > confusion it can cause, e.g. which column do I look at?
>
> The one that's documented.

Well, that assumes people read the documention and don't just do \d.
Keeping cruft around over time makes the system more complex.

> Wasn't newsysviews supposed to deal with these sorts of issues? Why
> were they rejected?

No idea.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

Excerpts from Bruce Momjian's message of mié jul 13 15:24:35 -0400 2011:
> David E. Wheeler wrote:
> > On Jul 13, 2011, at 12:14 PM, Bruce Momjian wrote:
> >
> > >> I'm wondering if it would be possible to restore the relistemp column
> > >> to pg_class, at least for backwards compatibility, so that apps that
> > >> expected it can continue to work on both 9.0 and 9.1. Even if it's
> > >> read-only somehow, and the same as `relpersistence <> 't'`.
> > >
> > > Uh, that is going to require an initdb, and it is unlinkely we are going
> > > to need that this far into 9.1 beta.
> >
> > I was afraid of that.
> >
> > > Also, we don't normally keep
> > > system table columns around for backward compatibility because of the
> > > confusion it can cause, e.g. which column do I look at?
> >
> > The one that's documented.
>
> Well, that assumes people read the documention and don't just do \d.
> Keeping cruft around over time makes the system more complex.

This seems a case where column synonyms would have been useful (as was
the procpid / pid change).

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Jul 13, 2011, at 12:38 PM, Alvaro Herrera wrote:

>> Well, that assumes people read the documention and don't just do \d.
>> Keeping cruft around over time makes the system more complex.
>
> This seems a case where column synonyms would have been useful (as was
> the procpid / pid change).

Well it couldn't just be that, because the data type has changed, too. Unless you could make a kind of "view column" or something where the expression was `relpersistence <> 't'`.

Best,

David

"David E. Wheeler" <david(at)kineticode(dot)com> wrote:

> Unless you could make a kind of "view column" or something where the

> expression was `relpersistence <> 't'`.

create or replace function relistemp(rel pg_class)
returns boolean language sql immutable strict as
$$select $1.relpersistence = 't';$$;

Just don't forget to use the table name or alias in front of it... :-)

-Kevin

On Jul 13, 2011, at 12:57 PM, Kevin Grittner wrote:

> create or replace function relistemp(rel pg_class)
> returns boolean language sql immutable strict as
> $$select $1.relpersistence = 't';$$;
>
> Just don't forget to use the table name or alias in front of it... :-)

Oh, nice hack. How far back does that work (pgTAP runs on 8.0 and higher)?

Thanks,

David

On 14.07.2011 19:51, David E. Wheeler wrote:
> On Jul 13, 2011, at 12:57 PM, Kevin Grittner wrote:
>
>> create or replace function relistemp(rel pg_class)
>> returns boolean language sql immutable strict as
>> $$select $1.relpersistence = 't';$$;
>>
>> Just don't forget to use the table name or alias in front of it... :-)
>
> Oh, nice hack. How far back does that work (pgTAP runs on 8.0 and higher)?

Far back. But you only need it in >= 9.1. Older versions have the
pg_class.relistemp column anyway.

Not sure how this helps, though. If you modify pgTAP to install that
automatically in pgTAP when dealing with a new server version, you might
as well modify its queries to use relispersistence = 't' directly when
dealing with a new server version. It works as a manual work-around if
you can't upgrade pgTAP, I guess.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

"David E. Wheeler" <david(at)kineticode(dot)com> wrote:
> On Jul 13, 2011, at 12:57 PM, Kevin Grittner wrote:
>
>> create or replace function relistemp(rel pg_class)
>> returns boolean language sql immutable strict as
>> $$select $1.relpersistence = 't';$$;
>>
>> Just don't forget to use the table name or alias in front of
>> it... :-)
>
> Oh, nice hack. How far back does that work (pgTAP runs on 8.0 and
> higher)?

As far as I know, the technique of creating a function with a record
type as its only parameter to use as a "generated column" goes way
back. This particular function won't work prior to 9.1, because you
won't have the relpersistence column, but then, prior to 9.1 you
wouldn't need to run this because you already have a relistemp
column.

-Kevin

On Jul 14, 2011, at 9:55 AM, Heikki Linnakangas wrote:

> Far back. But you only need it in >= 9.1. Older versions have the pg_class.relistemp column anyway.

Yeah.

> Not sure how this helps, though. If you modify pgTAP to install that automatically in pgTAP when dealing with a new server version, you might as well modify its queries to use relispersistence = 't' directly when dealing with a new server version. It works as a manual work-around if you can't upgrade pgTAP, I guess.

Yeah, that's what I'd rather avoid. I'll probably have to modify the function that makes the call to look at the version number. Annoying, but do-able.

https://github.com/theory/pgtap/blob/master/sql/pgtap.sql.in#L5894

Best,

David

All,

BTW, if we're dumping relistemp, we're going to need to notify every
maker of a PostgreSQL admin interface before we release 9.1.

This is why we should have had a complete set of sysviews ...

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

Josh Berkus wrote:
> All,
>
> BTW, if we're dumping relistemp, we're going to need to notify every
> maker of a PostgreSQL admin interface before we release 9.1.
>
> This is why we should have had a complete set of sysviews ...

Are they not testing our 9.1 betas?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> Josh Berkus wrote:
>> BTW, if we're dumping relistemp, we're going to need to notify every
>> maker of a PostgreSQL admin interface before we release 9.1.

> Are they not testing our 9.1 betas?

There has never, ever, been a guarantee that the system catalogs don't
change across versions. Anybody issuing such queries should expect to
have to retest them and possibly change them in each new major release.
I see nothing to sweat about here.

regards, tom lane

On Jul 14, 2011, at 12:19 PM, Tom Lane wrote:

>> Are they not testing our 9.1 betas?
>
> There has never, ever, been a guarantee that the system catalogs don't
> change across versions. Anybody issuing such queries should expect to
> have to retest them and possibly change them in each new major release.
> I see nothing to sweat about here.

A deprecation cycle at least might be useful.

Best,

David

"David E. Wheeler" <david(at)kineticode(dot)com> wrote:

> A deprecation cycle at least might be useful.

How about a "relistemp" extension on pgxn.org for the "generated
column" function to provide the backward compatibility? Is the new
extension mechanism a sane way to help those who need a phase-out
period?

-Kevin

> There has never, ever, been a guarantee that the system catalogs don't
> change across versions. Anybody issuing such queries should expect to
> have to retest them and possibly change them in each new major release.

I know that's always been our policy. It his, however,
vendor-unfriendly because we don't supply any interface for many things
(such as temp tables) other than the system catalogs.

So if we're going to break compatibility, then we could stand to make a
little noise about it.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On Thu, Jul 14, 2011 at 21:59, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>
>> There has never, ever, been a guarantee that the system catalogs don't
>> change across versions.  Anybody issuing such queries should expect to
>> have to retest them and possibly change them in each new major release.
>
> I know that's always been our policy.  It his, however,
> vendor-unfriendly because we don't supply any interface for many things
> (such as temp tables) other than the system catalogs.
>
> So if we're going to break compatibility, then we could stand to make a
> little noise about it.

We've broken the admin apps in pretty much every single release. And
they generally don't complain. If someone developing an admin app
hasn't been doing extensive testing starting *at the latest* with
beta1 (and recommended per each alpha), they shouldn't expect to
release until quite long after the release.

That said, a stable set of system views certainly wouldn't hurt - but
making extra noise about a simple change like this one would likely
not make a difference.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

On Thursday, July 14, 2011, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>
>> There has never, ever, been a guarantee that the system catalogs don't
>> change across versions.  Anybody issuing such queries should expect to
>> have to retest them and possibly change them in each new major release.
>
> I know that's always been our policy.  It his, however,
> vendor-unfriendly because we don't supply any interface for many things
> (such as temp tables) other than the system catalogs.
>
> So if we're going to break compatibility, then we could stand to make a
> little noise about it.

As one of said vendors, I completely disagree. There are a ton of
things that change with each release, and all we do by putting in
hacks for backwards compatibility is add bloat that needs to be
maintained, and encourage vendors to be lazy.

Break compatibility is actually something that is important to us - it
forces us to fix obvious issues, and makes it much harder to
inadvertently miss important changes.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Jul 14, 2011, at 2:10 PM, Dave Page wrote:

> Break compatibility is actually something that is important to us - it
> forces us to fix obvious issues, and makes it much harder to
> inadvertently miss important changes.

Agreed, but a deprecation cycle would be much appreciated.

David

> As one of said vendors, I completely disagree.

I don't agree that you qualify as a vendor. You're on the friggin' core
team.

I'm talking about vendors like DBVizualizer or TORA, for which
PostgreSQL is just one of the databases they support. If stuff breaks
gratuitously, the reaction of some of them is always to either drop
support or delay it for a year or more. This doesn't benefit our community.

> There are a ton of
> things that change with each release, and all we do by putting in
> hacks for backwards compatibility is add bloat that needs to be
> maintained, and encourage vendors to be lazy.

I don't agree that having comprehensive system views with multi-version
stability would be a "hack".

> Break compatibility is actually something that is important to us - it
> forces us to fix obvious issues, and makes it much harder to
> inadvertently miss important changes.

What I'm hearing from you is: "Breaking backwards compatibility is
something we should do more of because it lets us know which vendors are
paying attention and weeds out the unfit." Is that what you meant to say?

That seems like a way to ensure that PostgreSQL support continue to be
considered optional, or based on outdated versions, for multi-database
tools.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

Josh Berkus <josh(at)agliodbs(dot)com> writes:
>> There are a ton of
>> things that change with each release, and all we do by putting in
>> hacks for backwards compatibility is add bloat that needs to be
>> maintained, and encourage vendors to be lazy.

> I don't agree that having comprehensive system views with multi-version
> stability would be a "hack".

If we had that, it wouldn't be a hack. Putting in a hack to cover the
specific case of relistemp, on the other hand, is just a hack.

The real question here, IMO, is "how many applications are there that
really need to know about temporary relations, but have no interest in
the related feature of unlogged relations?". Because only such apps
would be served by a compatibility hack for this. An app that thinks it
knows the semantics of relistemp, and isn't updated to grok unlogged
tables, may be worse than broken --- it may be silently incorrect.

regards, tom lane

>> I don't agree that having comprehensive system views with multi-version
>> stability would be a "hack".
>
> If we had that, it wouldn't be a hack. Putting in a hack to cover the
> specific case of relistemp, on the other hand, is just a hack.

I agree.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On Jul 14, 2011, at 3:05 PM, Tom Lane wrote:

> Josh Berkus <josh(at)agliodbs(dot)com> writes:
>>> There are a ton of
>>> things that change with each release, and all we do by putting in
>>> hacks for backwards compatibility is add bloat that needs to be
>>> maintained, and encourage vendors to be lazy.
>
>> I don't agree that having comprehensive system views with multi-version
>> stability would be a "hack".
>
> If we had that, it wouldn't be a hack.

Is that an endorsement for adding such a feature?

> Putting in a hack to cover the
> specific case of relistemp, on the other hand, is just a hack.

Sure.

> The real question here, IMO, is "how many applications are there that
> really need to know about temporary relations, but have no interest in
> the related feature of unlogged relations?". Because only such apps
> would be served by a compatibility hack for this. An app that thinks it
> knows the semantics of relistemp, and isn't updated to grok unlogged
> tables, may be worse than broken --- it may be silently incorrect.

So pgTAP creates temporary tables to store result sets so that it can then compare the results of two queries. The function in question was getting a list of columns in such a temporary table in order to make sure that the types were the same between two such tables before comparing results. It checked relistemp to make sure it was looking at the temp table rather than some other table that might happen to have the same name.

So now the query looks like this:

SELECT pg_catalog.format_type(a.atttypid, a.atttypmod)
FROM pg_catalog.pg_attribute a
JOIN pg_catalog.pg_class c ON a.attrelid = c.oid
WHERE c.relname = $1
-- AND c.relistemp -- 8.3-9.0
AND c.relpersistence = 't' -- 9.1
AND attnum > 0
AND NOT attisdropped
ORDER BY attnum

Is that all I need to do, or is there something else I should be aware of with regard to unlogged tables?

Thanks,

David

On Jul 14, 2011, at 5:13 PM, "David E. Wheeler" <david(at)kineticode(dot)com> wrote:
> On Jul 14, 2011, at 3:05 PM, Tom Lane wrote:
>
>> Josh Berkus <josh(at)agliodbs(dot)com> writes:
>>>> There are a ton of
>>>> things that change with each release, and all we do by putting in
>>>> hacks for backwards compatibility is add bloat that needs to be
>>>> maintained, and encourage vendors to be lazy.
>>
>>> I don't agree that having comprehensive system views with multi-version
>>> stability would be a "hack".
>>
>> If we had that, it wouldn't be a hack.
>
> Is that an endorsement for adding such a feature?
>
>> Putting in a hack to cover the
>> specific case of relistemp, on the other hand, is just a hack.
>
> Sure.
>
>> The real question here, IMO, is "how many applications are there that
>> really need to know about temporary relations, but have no interest in
>> the related feature of unlogged relations?". Because only such apps
>> would be served by a compatibility hack for this. An app that thinks it
>> knows the semantics of relistemp, and isn't updated to grok unlogged
>> tables, may be worse than broken --- it may be silently incorrect.
>
> So pgTAP creates temporary tables to store result sets so that it can then compare the results of two queries. The function in question was getting a list of columns in such a temporary table in order to make sure that the types were the same between two such tables before comparing results. It checked relistemp to make sure it was looking at the temp table rather than some other table that might happen to have the same name.
>
> So now the query looks like this:
>
> SELECT pg_catalog.format_type(a.atttypid, a.atttypmod)
> FROM pg_catalog.pg_attribute a
> JOIN pg_catalog.pg_class c ON a.attrelid = c.oid
> WHERE c.relname = $1
> -- AND c.relistemp -- 8.3-9.0
> AND c.relpersistence = 't' -- 9.1
> AND attnum > 0
> AND NOT attisdropped
> ORDER BY attnum
>
> Is that all I need to do, or is there something else I should be aware of with regard to unlogged tables?

Probably not, in this case. Just a thought: maybe you could rewrite the query to check whether the namespace name starts with pg_temp. Maybe that would be version-independent...

...Robert

On Jul 14, 2011, at 6:43 PM, Robert Haas wrote:

>> Is that all I need to do, or is there something else I should be aware of with regard to unlogged tables?
>
> Probably not, in this case. Just a thought: maybe you could rewrite the query to check whether the namespace name starts with pg_temp. Maybe that would be version-independent...

Ah, good idea, I forgot about pg_temp.

David

On Thu, Jul 14, 2011 at 10:54 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>
>> As one of said vendors, I completely disagree.
>
> I don't agree that you qualify as a vendor.  You're on the friggin' core
> team.

And I look after the development of the leading open source management
tool for PostgreSQL, as well as a number of tools at EnterpriseDB. I
might be on the core team, but I still build tools.

> I'm talking about vendors like DBVizualizer or TORA, for which
> PostgreSQL is just one of the databases they support.  If stuff breaks
> gratuitously, the reaction of some of them is always to either drop
> support or delay it for a year or more.  This doesn't benefit our community.

I'm not talking about gratuitously breaking things - just not masking
essential changes.

>> There are a ton of
>> things that change with each release, and all we do by putting in
>> hacks for backwards compatibility is add bloat that needs to be
>> maintained, and encourage vendors to be lazy.
>
> I don't agree that having comprehensive system views with multi-version
> stability would be a "hack".

That isn't what was being suggested.

>> Break compatibility is actually something that is important to us - it
>> forces us to fix obvious issues, and makes it much harder to
>> inadvertently miss important changes.
>
> What I'm hearing from you is: "Breaking backwards compatibility is
> something we should do more of because it lets us know which vendors are
> paying attention and weeds out the unfit."   Is that what you meant to say?

No, I meant to say precisely what I did say. By not masking changes in
the catalogs, we draw attention to things that have changed for good
reason, and almost certainly need to be addressed.

> That seems like a way to ensure that PostgreSQL support continue to be
> considered optional, or based on outdated versions, for multi-database
> tools.

Whilst this particular case might be safe to just ignore in third part
tools, other changes to the catalogs are not, and masking them could
potentially hide bugs or issues that need to be fixed to actually work
properly with the newer version of the server.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Magnus Hagander <magnus(at)hagander(dot)net> writes:
> On Thu, Jul 14, 2011 at 21:59, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>> So if we're going to break compatibility, then we could stand to make a
>> little noise about it.

> We've broken the admin apps in pretty much every single release. And
> they generally don't complain.

Yeah. Quite honestly, this thread is trying to turn a molehill into a
mountain. I will confidently predict that the really big, nasty change
in 9.1 is the change of default standard_conforming_strings. That's
going to break way more apps than anything else, and possibly in
security-critical ways. Anybody who moves an app onto 9.1 without
testing it is going to suffer big-time.

regards, tom lane

On Fri, Jul 15, 2011 at 17:25, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Magnus Hagander <magnus(at)hagander(dot)net> writes:
>> On Thu, Jul 14, 2011 at 21:59, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>>> So if we're going to break compatibility, then we could stand to make a
>>> little noise about it.
>
>> We've broken the admin apps in pretty much every single release. And
>> they generally don't complain.
>
> Yeah.  Quite honestly, this thread is trying to turn a molehill into a
> mountain.  I will confidently predict that the really big, nasty change
> in 9.1 is the change of default standard_conforming_strings.  That's
> going to break way more apps than anything else, and possibly in
> security-critical ways.  Anybody who moves an app onto 9.1 without
> testing it is going to suffer big-time.

+(a lot)

in fact, I think we should definitely "shout out" more clearly about
that one in the release notes. Yes, it's the very first item. But I
think it deserves a big fat warning box as well.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

"David E. Wheeler" <david(at)kineticode(dot)com> writes:
> So pgTAP creates temporary tables to store result sets so that it can then compare the results of two queries. The function in question was getting a list of columns in such a temporary table in order to make sure that the types were the same between two such tables before comparing results. It checked relistemp to make sure it was looking at the temp table rather than some other table that might happen to have the same name.

Well, actually, that code flat out doesn't work, so whether relistemp is
available in 9.1 is the least of your problems. Consider what would
happen if two concurrent sessions did this with the same temp table
name.

How about doing this instead?

SELECT pg_catalog.format_type(a.atttypid, a.atttypmod)
FROM pg_catalog.pg_attribute a
JOIN pg_catalog.pg_class c ON a.attrelid = c.oid
WHERE c.oid = 'pg_temp.tablenamehere'::pg_catalog.regclass
AND attnum > 0
AND NOT attisdropped
ORDER BY attnum

This would only work in releases that know the pg_temp abbreviation,
which includes any minor release later than March 2007. But since
relistemp doesn't even exist before 8.4 (released in 2009), that's still
more backwards-portable than what you've got. You could also just do
'tablenamehere'::pg_catalog.regclass and trust that the user didn't move
pg_temp to the back of the search path.

regards, tom lane

On Jul 13, 2011, at 2:23 PM, David E. Wheeler wrote:
> Wasn't newsysviews supposed to deal with these sorts of issues? Why were they rejected?

Unless they recently came up again and got rejected again; the original complaint was that some of their conventions didn't follow information_schema conventions. The community wanted that changed and that never happened.
--
Jim C. Nasby, Database Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net

On Fri, Jul 15, 2011 at 8:17 PM, Jim Nasby <jim(at)nasby(dot)net> wrote:
> On Jul 13, 2011, at 2:23 PM, David E. Wheeler wrote:
>> Wasn't newsysviews supposed to deal with these sorts of issues? Why were they rejected?
>
> Unless they recently came up again and got rejected again; the original complaint was that some of their conventions didn't follow information_schema conventions. The community wanted that changed and that never happened.

I think, also, that the idea that newsysviews is going to fix all of
our problems is mostly wishful thinking. Let's suppose that we had a
system view over pg_class that kept around some variant of relistemp
even though it's gone from pg_class per se. Well, such a column would
probably be false for both unlogged and permanent tables and true for
temporary tables and David would be happy.

But what happens when and if we add global temporary tables? Now we
might very well decide to set the faux-relistemp to true for temporary
and global temporary tables (they do have "temporary" in the name,
after all!) and false for unlogged and permanent tables. Or we might
decide that the faux-relistemp should only be true for the kind of
temporary tables that we've always had, and false for these new global
temporary tables, perhaps on the theory that a global temporary table
is not really temporary at all, though its contents are. One of these
decisions would probably be right for David (and pgTap) and the other
would be wrong; and the decision that was right for pgTap might be
wrong for some other client. So instead of breaking pgTap we might
just quietly make it stop working correctly.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Jul 16, 2011, at 7:16 PM, Robert Haas wrote:

> But what happens when and if we add global temporary tables? Now we
> might very well decide to set the faux-relistemp to true for temporary
> and global temporary tables (they do have "temporary" in the name,
> after all!) and false for unlogged and permanent tables. Or we might
> decide that the faux-relistemp should only be true for the kind of
> temporary tables that we've always had, and false for these new global
> temporary tables, perhaps on the theory that a global temporary table
> is not really temporary at all, though its contents are. One of these
> decisions would probably be right for David (and pgTap) and the other
> would be wrong; and the decision that was right for pgTap might be
> wrong for some other client. So instead of breaking pgTap we might
> just quietly make it stop working correctly.

Well I think it would continue to work exactly as it has in the past. And if one needed to know other information about the *type* of temp table, well then one would have to use relpersistence.

The idea is not to try to make it adapt to future changes. The idea is to try to preserve the previous behavior for some period of time.

Best,

David

On Jul 15, 2011, at 9:41 AM, Tom Lane wrote:

> Well, actually, that code flat out doesn't work, so whether relistemp is
> available in 9.1 is the least of your problems. Consider what would
> happen if two concurrent sessions did this with the same temp table
> name.

Oh. Duh.

> How about doing this instead?
>
> SELECT pg_catalog.format_type(a.atttypid, a.atttypmod)
> FROM pg_catalog.pg_attribute a
> JOIN pg_catalog.pg_class c ON a.attrelid = c.oid
> WHERE c.oid = 'pg_temp.tablenamehere'::pg_catalog.regclass
> AND attnum > 0
> AND NOT attisdropped
> ORDER BY attnum

I always forget that "$schema.$tablename"::regclass will work.

> This would only work in releases that know the pg_temp abbreviation,
> which includes any minor release later than March 2007. But since
> relistemp doesn't even exist before 8.4 (released in 2009), that's still
> more backwards-portable than what you've got. You could also just do
> 'tablenamehere'::pg_catalog.regclass and trust that the user didn't move
> pg_temp to the back of the search path.

Yeah, this is a much better solution. Many thanks, Tom, just what I needed.

Best,

David