dump/restore needed when switching from 32bit to 64bit processor architecture?

Hi!

I've already asked this question a month ago -
it remains unanswered.
Dare to repeat since this issue is rather important for me.

So.

When I invoke

select 1 where 5 in (null)

everything is Ok,
though when I try

select 1 where 5 in (select null)

I get

ERROR: failed to find conversion function from "unknown" to integer.

Is it desired behavior or subject to change
in future versions of PG?

I realize my simplified example looks radiculous while it came from reality.
Sometimes my engine generates SQL queries does not know correct type
and unable to cast, while PostgreSQL should have all information required.

Thanks.

--
Best regards
Ilja Golshtein

Ilja Golshtein wrote:

When I invoke

select 1 where 5 in (null)

everything is Ok,
though when I try

select 1 where 5 in (select null)

I get

ERROR: failed to find conversion function from "unknown" to integer.

Is it desired behavior or subject to change
in future versions of PG?

Well, it would obviously be better if PG could figure out it was safe,
but I'm not sure there's a general case where it is. You can see it's OK
because you know there's only one row in your SELECT result-set.
However, what if you had:
... (select null UNION ALL select '2005-01-01')
Now is this a set of "unknown" or "text" or "date"? Should PG change
it's decision based on what the rest of the query wants?

So - although this may change in future versions of PG, I wouldn't count
on it never being a problem.

I realize my simplified example looks radiculous while it came from reality.
Sometimes my engine generates SQL queries does not know correct type
and unable to cast, while PostgreSQL should have all information required.

How are you generating the SQL if you don't know the types involved? Are
you just blindly quoting all values? What do you do with arrays/points
etc, or don't you handle those?

--
Richard Huxton
Archonet Ltd

Well, it would obviously be better if PG could figure out it was safe,
but I'm not sure there's a general case where it is. You can see it's OK
because you know there's only one row in your SELECT result-set.

I think, it's OK because NULL can be compared with anything
with predictable result and no additional information about
types is necessary.
Is it correct vision?

I agree it's hard to proceed your query with UNION
and some sort of error is reasonable here.

--
Best regards
Ilja Golshtein

=?ISO-8859-1?Q?Dirk_Lutzeb=E4ck?= <lutzeb@aeccom.com> writes:

when have a 8.0.3 database running on a XEON machine. We want to replace
it with an Opteron where postgresql is to be compiled with 64bit. Do we
need a dump/restore or can we just start the db with the new compilation?

I'd bet you need a dump/restore --- MAXALIGN is most likely different
on the two platforms. If it isn't, then maybe you could get away with
this, but it's definitely risky.

regards, tom lane

"Ilja Golshtein" <ilejn@yandex.ru> writes:

Well, it would obviously be better if PG could figure out it was safe,
but I'm not sure there's a general case where it is. You can see it's OK
because you know there's only one row in your SELECT result-set.

I think, it's OK because NULL can be compared with anything
with predictable result and no additional information about
types is necessary.
Is it correct vision?

The backend doesn't really distinguish NULL from 'foo' (or untyped
string literals in general) when making datatype decisions. If we
were to change the behavior of
select 1 where 5 in (select null)
at all, it would undoubtedly be to treat it as
select 1 where 5 in (select null::text)
because TEXT is the default resolution for UNKNOWN in every other
case where we force a choice to be made. But this is not what you
want for your example, and in general it would probably break as
many cases as it fixed. So I'm inclined to leave it as-is ---
an error message is probably better than a surprising silent choice.

My recommendation is to cast the NULL to the right type explicitly.

regards, tom lane

Ilja Golshtein wrote:

Well, it would obviously be better if PG could figure out it was safe,
but I'm not sure there's a general case where it is. You can see it's OK
because you know there's only one row in your SELECT result-set.

I think, it's OK because NULL can be compared with anything
with predictable result and no additional information about
types is necessary.
Is it correct vision?

Yes*, but you've not got a single NULL in your examples, you've got a
set of rows containing one unnamed column with an unspecified type. That
set happens to have only one row and that contains a NULL.

I agree it's hard to proceed your query with UNION
and some sort of error is reasonable here.

But from outside the brackets, they look the same.

What would happen ideally, is that PG would notice we have a single row
and column here and collapse this down to a single scalar value.
However, I'm not sure under what circumstances it can do so (or does),
or whether it is cost-effective.

[* Actually, I think NULLs are typed in SQL, which means you should be
able to get type violations. ]

--
Richard Huxton
Archonet Ltd

Hello!

Well, it would obviously be better if PG could figure out it was safe,
but I'm not sure there's a general case where it is. You can see it's OK
because you know there's only one row in your SELECT result-set.

I think, it's OK because NULL can be compared with anything
with predictable result and no additional information about
types is necessary.
Is it correct vision?

The backend doesn't really distinguish NULL from 'foo' (or untyped
string literals in general) when making datatype decisions.

I think when PG is about to compare object of known type and known
value (it is 5 in my example) with object with unknown type
but known value or known null flag (it is null in my example) it is
high time to return 'false' instead of producing error.

As far as I understand, it is not about comparision only,
but about any operation.

I really believe NULLs are special here.

Of course I am a complete stranger and unaware of PostgreSQL internals
so what I am saying may contradict with some basical concepts. Looks like
it does since according to Tom, PostgreSQL does not treat null literals
in special way :(

Thanks.

--
Best regards
Ilja Golshtein

Richard Huxton <dev@archonet.com> writes:

[* Actually, I think NULLs are typed in SQL, which means you should be
able to get type violations. ]

I'm pretty sure the entire construct is illegal per a strict reading of
the SQL spec --- the spec only allows NULL to appear in contexts where a
datatype can be assigned to it immediately. Per spec you'd have to
write this as
select 1 where 5 in (select cast(null as integer));

In the spec, NULL is not a general <expression>, it's a <contextually
typed value expression>, and those are only allowed as the immediate
argument of a CAST(), the immediate column value of an INSERT or UPDATE,
and one or two other very circumscribed cases. SQL99 section 6.4 is
very clear about what they intend:

2) The declared type DT of a <null specification> NS is determined
by the context in which NS appears. NS is effectively replaced
by CAST ( NS AS DT ).

NOTE 70 - In every such context, NS is uniquely associated with
some expression or site of declared type DT, which thereby
becomes the declared type of NS.

PG's ability to infer a type for a NULL constant goes well beyond what
the spec allows --- but it does have limits.

regards, tom lane

Richard Huxton <dev@archonet.com> writes:

[* Actually, I think NULLs are typed in SQL, which means you should be
able to get type violations. ]

I'm pretty sure the entire construct is illegal per a strict reading of
the SQL spec --- the spec only allows NULL to appear in contexts where a
datatype can be assigned to it immediately.

Honestly I cannot say this info changes too much for me (other DBMSs I use as backend handle this untyped NULLs), though the situation is clarified.

Thanks a lot.

--
Best regards
Ilja Golshtein

Thanks Tom,

we now stay with 32bit to allow backward compatibilty with XEON which is
needed as a fail-over system.

The question is which gcc cflags are best used with XEON and Opteron to
achieve fail-over compatibility. This is what we used for postgresql 8.0.3:

XEON, RHEL 3.0 AS:
CFLAGS = "-mcpu=pentium4 -march=pentium4"

Opteron 875, RHEL 3.0 AS, gcc version 3.2.3 20030502 (Red Hat Linux
3.2.3-42):
CFLAGS = "-Acpu=x86_64 -Amachine=x86_64"

Do we still need a dump/restore with this config?

Regards,

Dirk

Tom Lane wrote:

Dirk Lutzeb�ck wrote:

The question is which gcc cflags are best used with XEON and Opteron
to achieve fail-over compatibility.

Configure both ways and run a diff over the generated pg_config.h files.
If there are differences, then there's a potential for trouble. (No
differences don't say there won't be trouble, but it'd be far less
likely.)

--
Peter Eisentraut
http://developer.postgresql.org/~petere/