7.4 - FK constraint performance

  • Permalink
  • submit to reddit
  • Email
  • Follow


PostgreSQL 7.4 on i386-linux-gnu, compiled by GCC 2.96

-- about 10 records
CREATE TABLE my.Small
(
  id my.dint NOT NULL,
  code my.dvalue NOT NULL,
  CONSTRAINT pk_1 PRIMARY KEY (id),
) WITHOUT OIDS;

-- about 80M rows
CREATE TABLE my.Large 
(
  id my.dlong NOT NULL, 
  small_id my.dint NOT NULL,
  value my.value,
  CONSTRAINT pk_2 PRIMARY KEY (id),
  CONSTRAINT fk_2 FOREIGN KEY (small_id) REFERENCES my.small (id) ON UPDATE
RESTRICT ON DELETE RESTRICT,
) WITHOUT OIDS;

CREATE INDEX small_fk  ON my.Large USING btree (small_id);

---------------------------------------------
The fowllowing queiries run in less than 40 ms.
1) select 1 from Large where small_id = 239
2) SELECT 1 FROM ONLY "my"."Large" x WHERE "small_id" = 239 FOR UPDATE OF x

3) delete from Small where id = 239 
Feb 11 18:07:36 srv07 postgres[2091]: [91-1] LOG:  statement: SELECT 1 FROM
ONLY "my"."Large" x WHERE "small_id" = $1 FOR UPDATE OF x Feb 11 18:10:41 srv07

postgres[2091]: [92-1] LOG: duration: 185273.262 ms

When I try to delete record, it takes > 3 min. Why is it taking so long if 
practically the same select query (see (2)) is running very quickly. Anything
that can be done to fix it?

Thanks





__________________________________
Do you Yahoo!?
Yahoo! Finance: Get your refund fast by filing online.
http://taxes.yahoo.com/filing.html

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to majordomo@postgresql.org so that your
      message can get through to the mailing list cleanly

0
Reply oneway_111 2/12/2004 12:06:10 AM

See related articles to this posting


On Wed, 11 Feb 2004, ow wrote:

> PostgreSQL 7.4 on i386-linux-gnu, compiled by GCC 2.96
>
> -- about 10 records
> CREATE TABLE my.Small
> (
>   id my.dint NOT NULL,
>   code my.dvalue NOT NULL,
>   CONSTRAINT pk_1 PRIMARY KEY (id),
> ) WITHOUT OIDS;
>
> -- about 80M rows
> CREATE TABLE my.Large
> (
>   id my.dlong NOT NULL,
>   small_id my.dint NOT NULL,
>   value my.value,
>   CONSTRAINT pk_2 PRIMARY KEY (id),
>   CONSTRAINT fk_2 FOREIGN KEY (small_id) REFERENCES my.small (id) ON UPDATE
> RESTRICT ON DELETE RESTRICT,
> ) WITHOUT OIDS;
>
> CREATE INDEX small_fk  ON my.Large USING btree (small_id);
>
> ---------------------------------------------
> The fowllowing queiries run in less than 40 ms.
> 1) select 1 from Large where small_id = 239
> 2) SELECT 1 FROM ONLY "my"."Large" x WHERE "small_id" = 239 FOR UPDATE OF x
>
> 3) delete from Small where id = 239
> Feb 11 18:07:36 srv07 postgres[2091]: [91-1] LOG:  statement: SELECT 1 FROM
> ONLY "my"."Large" x WHERE "small_id" = $1 FOR UPDATE OF x Feb 11 18:10:41 srv07
>
> postgres[2091]: [92-1] LOG: duration: 185273.262 ms
>
> When I try to delete record, it takes > 3 min. Why is it taking so long if
> practically the same select query (see (2)) is running very quickly. Anything
> that can be done to fix it?

Hmm, I'd wonder if maybe it's choosing a sequential scan in the second
case?  As a random question, does increasing the statistics target on
Large.small_id and re-analyzing change its behavior?

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faqs/FAQ.html

0
Reply sszabo 2/12/2004 1:06:36 AM

ow <oneway_111@yahoo.com> writes:
> When I try to delete record, it takes > 3 min. Why is it taking so long if 
> practically the same select query (see (2)) is running very quickly. Anything
> that can be done to fix it?

What are those column datatypes?

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

0
Reply tgl 2/12/2004 3:12:15 AM

--- Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:
> Hmm, I'd wonder if maybe it's choosing a sequential scan in the second
> case?  As a random question, does increasing the statistics target on
> Large.small_id and re-analyzing change its behavior?

Ran analyze, the result is the same. Here's more info:

1) There's 1 row in "Large" for "small_id" = 239 
SELECT 1 FROM ONLY "my"."Large" x WHERE "small_id" = 239 FOR UPDATE OF x

Quick query. Explain shows index scan.

2) There are many rows in "Large" for "small_id" = 1
SELECT 1 FROM ONLY "my"."Large" x WHERE "small_id" = 1 FOR UPDATE OF x

Runs for about 3 min. Explain shows table scan.

3) delete from Small where id = 239
Runs for about 3 min. It does appear that table scan is used for FK
verification. But why? Am deleting "id = 239"  not "id = 1" and the query in
(1) runs very quickly. Had suspicion that wrong id is passed during FK
verification but FK constraint DOES work.

4) Domain types used in the example above
my.dint = int
my.dlong = int8
my.dvalue =  varchar(15)

Thanks




__________________________________
Do you Yahoo!?
Yahoo! Finance: Get your refund fast by filing online.
http://taxes.yahoo.com/filing.html

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

0
Reply oneway_111 2/12/2004 2:05:16 PM

On Thu, 12 Feb 2004, ow wrote:

>
> --- Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:
> > Hmm, I'd wonder if maybe it's choosing a sequential scan in the second
> > case?  As a random question, does increasing the statistics target on
> > Large.small_id and re-analyzing change its behavior?
>
> Ran analyze, the result is the same. Here's more info:

You also did the alter table to up the statistics target on the column,
right?

> 3) delete from Small where id = 239
> Runs for about 3 min. It does appear that table scan is used for FK
> verification. But why? Am deleting "id = 239"  not "id = 1" and the query in
> (1) runs very quickly. Had suspicion that wrong id is passed during FK
> verification but FK constraint DOES work.

It doesn't plan it as id=239 but as id=$1 and then executes it with
$1=239.  The plan data gets reused for other id values if it needs the
same fk action again later in the session.

I'd hoped that upping the statistics target and re-analyzing would make it
choose an index scan for the case where it doesn't know what constant is
going to be used.  Hmm, what is the estimated cost difference and real
time difference on id=1 between seqscan and index scan (explain analyze
output with and without enable_seqscan=off should show you).

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

0
Reply sszabo 2/12/2004 3:37:32 PM

--- Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:
> You also did the alter table to up the statistics target on the column,
> right?

Not really. I did not change the the default stats settings in the
postgresql.conf. Not sure what needs to be changed, can you clarify?

Thanks




__________________________________
Do you Yahoo!?
Yahoo! Finance: Get your refund fast by filing online.
http://taxes.yahoo.com/filing.html

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

0
Reply oneway_111 2/12/2004 3:53:51 PM

ow <oneway_111@yahoo.com> writes:
> When I try to delete record, it takes > 3 min.

I think it must be using a seqscan for the foreign key check query.
Could you try this and show the results?

prepare foo(my.dint) as
SELECT 1 FROM ONLY "my"."large" x WHERE "small_id" = $1 FOR UPDATE OF x;

explain analyze execute foo(239);

When I try it I see an indexscan plan, but maybe there's some aspect of
your setup that's causing problems.

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org

0
Reply tgl 2/12/2004 3:54:20 PM

--- Tom Lane <tgl@sss.pgh.pa.us> wrote:
> ow <oneway_111@yahoo.com> writes:
> > When I try to delete record, it takes > 3 min.
> 
> I think it must be using a seqscan for the foreign key check query.
> Could you try this and show the results? 

1) SELECT 1 FROM ONLY "my"."large" x WHERE "small_id" = 201 FOR UPDATE OF x;

QUERY PLAN
Index Scan using small_fk on large x  (cost=0.00..6.01 rows=1 width=6) (actual
time=0.251..0.251 rows=0 loops=1)
  Index Cond: ((small_id)::integer = 201)
Total runtime: 0.338 ms

2) prepare foo(my.dint) as
SELECT 1 FROM ONLY "my"."large" x WHERE "small_id" = $1 FOR UPDATE OF x;

explain analyze execute foo(201);

QUERY PLAN
Seq Scan on large x  (cost=0.00..1787052.30 rows=7893843 width=6) (actual
time=210566.301..210566.301 rows=0 loops=1)
  Filter: ((small_id)::integer = ($1)::integer)
Total runtime: 210566.411 ms

Thanks



__________________________________
Do you Yahoo!?
Yahoo! Finance: Get your refund fast by filing online.
http://taxes.yahoo.com/filing.html

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match

0
Reply oneway_111 2/12/2004 9:49:23 PM

ow <oneway_111@yahoo.com> writes:
> --- Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I think it must be using a seqscan for the foreign key check query.

> 2) prepare foo(my.dint) as
> SELECT 1 FROM ONLY "my"."large" x WHERE "small_id" = $1 FOR UPDATE OF x;

> explain analyze execute foo(201);

> QUERY PLAN
> Seq Scan on large x  (cost=0.00..1787052.30 rows=7893843 width=6) (actual
> time=210566.301..210566.301 rows=0 loops=1)
>   Filter: ((small_id)::integer = ($1)::integer)
> Total runtime: 210566.411 ms

Well, there's the smoking gun all right.  Why does it think there are
going to be 7893843 matching rows!?  Could we see the pg_stats row for
the large.small_id column?

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org

0
Reply tgl 2/13/2004 12:00:25 AM

--- Tom Lane <tgl@sss.pgh.pa.us> wrote:
> According to this entry, your small_id column only contains the ten
> values 1..10, roughly evenly distributed.  So why are you probing for
> 239??

Let's say we have City (small) and Person (large) tables. A new city was added
(mistakenly) with id=239, it does not have any "persons" assigned yet. Hence,
we want to remove the wrong "city" record.

In any case, one can't remove record from "small" unless there are NO records
in "large", RI will not allow it. The initial problem was that I tried to
delete a record from "small" and it was taking about 3 min to do that.

> The planner is certainly going to estimate a probe for an unspecified
> value as retrieving 10% of the table, and under that assumption it's
> quite right to use a seqscan.

Sounds pretty bad for my case. Any way to avoid the 10% scan?

Thanks




__________________________________
Do you Yahoo!?
Yahoo! Finance: Get your refund fast by filing online.
http://taxes.yahoo.com/filing.html

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

0
Reply oneway_111 2/13/2004 1:03:41 AM

--=-Sm/Nk84uafeokC0Om5Ks
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

On Thu, 2004-02-12 at 20:10, Tom Lane wrote:
> ow <oneway_111@yahoo.com> writes:
> > Sounds pretty bad for my case. Any way to avoid the 10% scan?
>=20
> Can't see how we optimize your case without pessimizing more-common cases.
> Sorry.

Statistics say there are 10 values. Statistics list the 10 most common
values (all of them). Given this, would it not be reasonable to assume
that 239 is a recent addition (if there at all) to the table and not
very common?

--=20
Rod Taylor <rbt [at] rbt [dot] ca>

Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
PGP Key: http://www.rbt.ca/rbtpub.asc

--=-Sm/Nk84uafeokC0Om5Ks
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (FreeBSD)

iD8DBQBALDw46DETLow6vwwRAh/TAJwN7mPHV7gw+SSyNvAys5Yp0/SvAwCfT/tw
RGgwr0plIk8PGNWaCHu8T2o=
=IBl+
-----END PGP SIGNATURE-----

--=-Sm/Nk84uafeokC0Om5Ks--

0
Reply rbt 2/13/2004 2:53:45 AM

ow <oneway_111@yahoo.com> writes:
> --- Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Can't see how we optimize your case without pessimizing more-common cases.

> I think other RDBMSs simply use preset value instead of partial table
> scan when there's not enough stat info. Might be a better way.

The problem here cannot be described as "not enough stat info".  The
available stats are complete and they point very strongly to the
conclusion that searches in the large table should be seqscans.
To do otherwise would be folly in general, even if it happens to
be the correct thing in this particular example.

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to majordomo@postgresql.org so that your
      message can get through to the mailing list cleanly

0
Reply tgl 2/13/2004 4:49:56 AM

On Fri, 13 Feb 2004, Stephan Szabo wrote:

>
> On Fri, 13 Feb 2004, Tom Lane wrote:
>
> > Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> > > On Fri, 13 Feb 2004, Tom Lane wrote:
> > >> I was looking at that last night.  It seems like we could add a LIMIT at
> > >> least in some contexts.  In the case at hand, we're just going to error
> > >> out immediately if we find a matching row, and so there's no need for
> > >> FOR UPDATE, is there?
> >
> > > I think there still is, because a not yet committed transaction could have
> > > deleted them all in which case I think the correct behavior is to wait and
> > > if that transaction commits allow the action and if it rolls back to
> > > error.
> >
> > Good point.  Okay, we can't put in a LIMIT.  But we could still hack the
> > planner to prefer a fast-start plan by passing an out-of-band tuple
> > fraction, for those RI plans where it's appropriate.  That would not
> > affect correctness.
>
> Right, I can try to look through the stuff you pointed at in the previous
> message over the weekend.

It looks to me that we could make this available to SPI fairly simply by
taking the current version of the following four routines: planner,
pg_plan_query, _SPI_execute and SPI_prepare, renaming them and giving them
a planning tuple fraction as a parameter, change references to the other
routines to the new names and then making four new functions with the
current names that call the renamed versions. In all the cases other than
planner I think we can have the new version pass 0.0 and in the case of
planner either 0.1 or 0.0 based on the isCursor parameter.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

0
Reply sszabo 2/15/2004 4:41:57 PM

Hello,

I just discovered the following change to CHAR(n) (taken from varlena.com,
general bits, issue 62).

This will cause me serious heart-ache, back-ache and bug-ache if we upgrade
to 7.4. Is there any compatibility flag available to use pre-7.4 behaviour
for CHAR(n)?

Regards, John

==================



In 7.4, one of the changes was that the char(n) type now truncates trailing
spaces. This causes a problem for those of us using ::char(n) to create
fixed length fields. Creating fixed length field batch files are usually
required to interface with legacy systems.

In the example below, psql is called from a shell with tuples only (t) and
unaligned (A). The result of the selection creates a fixed width file.

One of the techniques used to create fixed length fields in 7.3 and earlier
was to cast the value to char(n). This along with the various to_char()
functions used to be able to create fixed length records. For example:

% psql -At > batch.out << END
	select accountid::char(30),
	   to_char( transaction_amount, 'FM000.00'),
	   (lastname || ',' || firstname )::char(40),
	   bankid::char(15),
	   to_char( now(), 'YYYYMMDD');
END
In 7.4 this no longer works. The fields created with the cast are no longer
fixed length. Instead of using the cast to make fixed length fields, use
rpad() and lpad() to do the same thing.
	rpad(string text, length integer  [, fill text])
	lpad(string text, length integer  [, fill text])
The previous selection should now be written as follows.

% psql -At > batch.out << END
	select rpad(accountid,30),
	   to_char( transaction_amount, 'FM000.00'),
	   rpad( (lastname || ',' || firstname ), 40),
	   rpad(bankid, 15),
	   to_char( now(), 'YYYYMMDD');
END


0
Reply news 2/16/2004 3:29:42 AM

"news.postgresql.org" <jlim@natsoft.com.my> writes:
> I just discovered the following change to CHAR(n) (taken from varlena.com,
> general bits, issue 62).

The description you quote doesn't appear to have much of anything to do
with the actual behavior of 7.4.

7.4 will trim trailing spaces when converting char(n) to varchar or
text, but the example query does not do that.  It just coerces query
output columns to char(n), and that works the same as it did before.
For instance

regression=# select 'zit'::char(77);
                                    bpchar
-------------------------------------------------------------------------------
 zit
(1 row)


			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

0
Reply tgl 2/17/2004 11:10:56 PM

This is an example of the problem.  It used to expand
the middle thing to 15.

elein=# select 'x' || ' '::char(15) || 'x';
 ?column? 
----------
 xx
(1 row)


On Tue, Feb 17, 2004 at 06:10:56PM -0500, Tom Lane wrote:
> "news.postgresql.org" <jlim@natsoft.com.my> writes:
> > I just discovered the following change to CHAR(n) (taken from varlena.com,
> > general bits, issue 62).
> 
> The description you quote doesn't appear to have much of anything to do
> with the actual behavior of 7.4.
> 
> 7.4 will trim trailing spaces when converting char(n) to varchar or
> text, but the example query does not do that.  It just coerces query
> output columns to char(n), and that works the same as it did before.
> For instance
> 
> regression=# select 'zit'::char(77);
>                                     bpchar
> -------------------------------------------------------------------------------
>  zit
> (1 row)
> 
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org

0
Reply elein 2/17/2004 11:17:28 PM

elein <elein@varlena.com> writes:
> This is an example of the problem.  It used to expand
> the middle thing to 15.

> elein=# select 'x' || ' '::char(15) || 'x';
>  ?column? 
> ----------
>  xx
> (1 row)

Still does, but then the spaces go away again when the value goes into
the concatenation, because concatenation is a text operator.

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org

0
Reply tgl 2/17/2004 11:40:49 PM

So the problem is there. But blaming it on char was wrong.
It should be blamed on the varchar change.  Hey, I thought
the truncation was for varchar and not text? It was for both?

It would be semantically tricky to change the operator.
The precendence is to convert to text.  Now with
the implicit update of the char(n) to text for the operator
"corrupts" the char() value.

elein

On Tue, Feb 17, 2004 at 06:40:49PM -0500, Tom Lane wrote:
> elein <elein@varlena.com> writes:
> > This is an example of the problem.  It used to expand
> > the middle thing to 15.
> 
> > elein=# select 'x' || ' '::char(15) || 'x';
> >  ?column? 
> > ----------
> >  xx
> > (1 row)
> 
> Still does, but then the spaces go away again when the value goes into
> the concatenation, because concatenation is a text operator.
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
>                http://archives.postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to majordomo@postgresql.org so that your
      message can get through to the mailing list cleanly

0
Reply elein 2/17/2004 11:56:11 PM

On Tue, 17 Feb 2004, Tom Lane wrote:

> elein <elein@varlena.com> writes:
> > This is an example of the problem.  It used to expand
> > the middle thing to 15.
> 
> > elein=# select 'x' || ' '::char(15) || 'x';
> >  ?column? 
> > ----------
> >  xx
> > (1 row)
> 
> Still does, but then the spaces go away again when the value goes into
> the concatenation, because concatenation is a text operator.

But then this:

select 'x'||' '||'x'

should produce xx, but it produces x x.


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

0
Reply scott 2/18/2004 12:07:24 AM

> select 'x'||' '||'x'
>
> should produce xx, but it produces x x.
>

INCORRECT

This
select 'x'||' '::char ||'x'

Should produce xx

This
select 'x'||' '||'x'
is restateable as select 'x'|| ' '::text ||'x'

And the || operand for text is not dropping the extra spaces hence correctly
x x

Terry Fielder
Manager Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
terry@greatgulfhomes.com
Fax: (416) 441-9085


> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of scott.marlowe
> Sent: Tuesday, February 17, 2004 7:07 PM
> To: Tom Lane
> Cc: elein; news.postgresql.org; pgsql-sql@postgresql.org
> Subject: Re: [SQL] CHAR(n) always trims trailing spaces in 7.4
>
>
> On Tue, 17 Feb 2004, Tom Lane wrote:
>
> > elein <elein@varlena.com> writes:
> > > This is an example of the problem.  It used to expand
> > > the middle thing to 15.
> >
> > > elein=# select 'x' || ' '::char(15) || 'x';
> > >  ?column?
> > > ----------
> > >  xx
> > > (1 row)
> >
> > Still does, but then the spaces go away again when the
> value goes into
> > the concatenation, because concatenation is a text operator.
>
> But then this:
>
> select 'x'||' '||'x'
>
> should produce xx, but it produces x x.
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

0
Reply terry 2/18/2004 12:23:44 AM

"scott.marlowe" <scott.marlowe@ihs.com> writes:
> But then this:
> select 'x'||' '||'x'
> should produce xx, but it produces x x.

No, because the imputed type of those literals is text.  You'd have to
cast the middle guy to char(n) explicitly to make its trailing spaces go
away when it's reconverted to text.

The real issue here is that trailing spaces in char(n) are semantically
insignificant according to the SQL spec.  The spec is pretty vague about
which operations should actually honor that insignificance --- it's
clear that comparisons should, less clear about other things.  I think
the 7.4 behavior is more consistent than what we had before, but I'm
willing to be persuaded to change it again if someone can give an
alternate definition that's more workable than this one.

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faqs/FAQ.html

0
Reply tgl 2/18/2004 12:25:47 AM

Apparently the ::char is cast to varchar and then text?
That explains x || ' ' || x

On Tue, Feb 17, 2004 at 05:07:24PM -0700, scott.marlowe wrote:
> On Tue, 17 Feb 2004, Tom Lane wrote:
> 
> > elein <elein@varlena.com> writes:
> > > This is an example of the problem.  It used to expand
> > > the middle thing to 15.
> > 
> > > elein=# select 'x' || ' '::char(15) || 'x';
> > >  ?column? 
> > > ----------
> > >  xx
> > > (1 row)
> > 
> > Still does, but then the spaces go away again when the value goes into
> > the concatenation, because concatenation is a text operator.
> 
> But then this:
> 
> select 'x'||' '||'x'
> 
> should produce xx, but it produces x x.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

0
Reply elein 2/18/2004 12:27:13 AM

elein <elein@varlena.com> writes:
> Apparently the ::char is cast to varchar and then text?

No, directly to text, because the || operator is defined as taking text
inputs.  But there's no practical difference between text and varchar on
this point.

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org

0
Reply tgl 2/18/2004 3:53:17 AM

On Wednesday 18 February 2004 00:25, Tom Lane wrote:
> "scott.marlowe" <scott.marlowe@ihs.com> writes:
> > But then this:
> > select 'x'||' '||'x'
> > should produce xx, but it produces x x.
>
> No, because the imputed type of those literals is text.  You'd have to
> cast the middle guy to char(n) explicitly to make its trailing spaces go
> away when it's reconverted to text.
>
> The real issue here is that trailing spaces in char(n) are semantically
> insignificant according to the SQL spec.  The spec is pretty vague about
> which operations should actually honor that insignificance --- it's
> clear that comparisons should, less clear about other things.  I think
> the 7.4 behavior is more consistent than what we had before, but I'm
> willing to be persuaded to change it again if someone can give an
> alternate definition that's more workable than this one.

[rant on]

I've never really understood the rationale behind char(n) in SQL databases 
(other than as backward compatibility with some old mainframe DB). 
Insignificant spaces? If it's not significant, why is it there? You could 
have a formatting rule that specifies left-aligned strings space-padded (as 
printf) but that's not the same as mucking about appending and trimming 
spaces.

The only sensible definition of char(n) that I can see would be:
A text value of type char(n) is always "n" characters in length. If you assign 
less than "n" characters, it is right-padded with spaces. In all other 
respects it behaves as any other text type of length "n" with right-trailing 
spaces.

[rant off - ah, feel better for that :-]

-- 
  Richard Huxton
  Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

0
Reply dev 2/18/2004 9:40:16 AM

Richard Huxton <dev@archonet.com> writes:
> I've never really understood the rationale behind char(n) in SQL databases 
> (other than as backward compatibility with some old mainframe DB). 

There are (or were) systems in which the benefit of using fixed-width
columns is a lot higher than it is in Postgres.  The spec is evidently
trying to cater to them.  Too bad the writers whacked the semantics
around so cruelly to do it :-(

> The only sensible definition of char(n) that I can see would be:
> A text value of type char(n) is always "n" characters in length.

If the SQL spec were willing to leave it at that, I'd be happy.  But
we've got this problem that the trailing spaces are supposed to be
insignificant in at least some contexts.  I find the pre-7.4 behavior
to be pretty inconsistent.  For example, 7.3 and 7.4 agree on this:

regression=# select ('foo   '::char(6)) = ('foo');
 ?column?
----------
 t
(1 row)

Now given the above, wouldn't it stand to reason that

regression=# select ('foo   '::char(6) || 'bar') = ('foo' || 'bar');
 ?column?
----------
 f
(1 row)

or how about

regression=# select ('bar' || 'foo   '::char(6)) = ('bar' || 'foo');
 ?column?
----------
 f
(1 row)

In 7.4 both of these do yield true.  A closely related example is

regression=# select ('foo   '::char(6)) = ('foo'::text);

which yields false in 7.3 and true in 7.4.

I don't object to revisiting the behavior again, but 7.3 was not so
ideal that I want to just go back to it.

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org

0
Reply tgl 2/18/2004 3:56:00 PM

On Sun, 15 Feb 2004, Stephan Szabo wrote:

> On Fri, 13 Feb 2004, Stephan Szabo wrote:
>
> >
> > On Fri, 13 Feb 2004, Tom Lane wrote:
> >
> > > Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> > > > On Fri, 13 Feb 2004, Tom Lane wrote:
> > > >> I was looking at that last night.  It seems like we could add a LIMIT at
> > > >> least in some contexts.  In the case at hand, we're just going to error
> > > >> out immediately if we find a matching row, and so there's no need for
> > > >> FOR UPDATE, is there?
> > >
> > > > I think there still is, because a not yet committed transaction could have
> > > > deleted them all in which case I think the correct behavior is to wait and
> > > > if that transaction commits allow the action and if it rolls back to
> > > > error.
> > >
> > > Good point.  Okay, we can't put in a LIMIT.  But we could still hack the
> > > planner to prefer a fast-start plan by passing an out-of-band tuple
> > > fraction, for those RI plans where it's appropriate.  That would not
> > > affect correctness.
> >
> > Right, I can try to look through the stuff you pointed at in the previous
> > message over the weekend.
>
> It looks to me that we could make this available to SPI fairly simply by
> taking the current version of the following four routines: planner,
> pg_plan_query, _SPI_execute and SPI_prepare, renaming them and giving them
> a planning tuple fraction as a parameter, change references to the other
> routines to the new names and then making four new functions with the
> current names that call the renamed versions. In all the cases other than
> planner I think we can have the new version pass 0.0 and in the case of
> planner either 0.1 or 0.0 based on the isCursor parameter.

I did this, and changed the foreign keys to use it, but I haven't managed
to build a fk case where I could actually detect a change in the plan
chosen.  Since the queries are only a simple scan on the one table I'm
wondering if it's basically just modifying both costs by the same value
which means there's no real effect at all.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org

0
Reply sszabo 2/18/2004 4:03:36 PM

Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> I did this, and changed the foreign keys to use it, but I haven't managed
> to build a fk case where I could actually detect a change in the plan
> chosen.  Since the queries are only a simple scan on the one table I'm
> wondering if it's basically just modifying both costs by the same value
> which means there's no real effect at all.

After fooling with this, I think you are right.  The planner is modeling
both cases as a linear slope from zero to estimated-total-cost, and
since the total number of tuples to be returned is the same, taking a
percentage won't change the outcome.

The only way we could improve the situation would be to somehow instruct
the planner that even though we don't know the parameter value to be
used when we are planning, it should expect that that value is *not* in
the table, rather than expecting that it has a distribution similar to
what is in the table.  Seems pretty messy.

I have occasionally speculated about postponing planning of
parameterized queries until they are first executed, and then using the
actual parameter values supplied in that first execution for purposes of
estimating costs and selectivity.  That would work pretty nicely in this
particular case, but in the real world I think it'd be mighty dangerous;
you could end up optimizing for an outlier case that isn't
representative of the queries you'll see later.

Another interesting line of thought is to let the user supply
representative values to be used for planning purposes.  In PREPARE,
you could imagine saying something like

	PREPARE myquery (int = 42, text = 'http://www...') AS
		SELECT ... where url like $2 ...

and then using the sample value 'http://www...' for purposes of
estimating the LIKE result.  Then it'd be on the user's head to pick
good representatives --- but he could make sure that they really were
representative, and not have to worry about luck of the draw from the
first live query.

I'm not sure if we could use such a feature automatically to bias FK
queries in the right direction, but it's something to think about.

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

0
Reply tgl 2/18/2004 5:27:29 PM

Also, to make char(n) even more annoying, I had the one character value "K"
stored in a column that was char(2).  When I pulled it from the database and
tried to compare it to a variable with a value of "K" it came out inequal.
Of course in mysql, that was not a problem.

Jeremy

-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Richard Huxton
Sent: Wednesday, February 18, 2004 4:40 AM
To: Tom Lane; scott.marlowe
Cc: elein; news.postgresql.org; pgsql-sql@postgresql.org
Subject: Re: [SQL] CHAR(n) always trims trailing spaces in 7.4


On Wednesday 18 February 2004 00:25, Tom Lane wrote:
> "scott.marlowe" <scott.marlowe@ihs.com> writes:
> > But then this:
> > select 'x'||' '||'x'
> > should produce xx, but it produces x x.
>
> No, because the imputed type of those literals is text.  You'd have to
> cast the middle guy to char(n) explicitly to make its trailing spaces go
> away when it's reconverted to text.
>
> The real issue here is that trailing spaces in char(n) are semantically
> insignificant according to the SQL spec.  The spec is pretty vague about
> which operations should actually honor that insignificance --- it's
> clear that comparisons should, less clear about other things.  I think
> the 7.4 behavior is more consistent than what we had before, but I'm
> willing to be persuaded to change it again if someone can give an
> alternate definition that's more workable than this one.

[rant on]

I've never really understood the rationale behind char(n) in SQL databases
(other than as backward compatibility with some old mainframe DB).
Insignificant spaces? If it's not significant, why is it there? You could
have a formatting rule that specifies left-aligned strings space-padded (as
printf) but that's not the same as mucking about appending and trimming
spaces.

The only sensible definition of char(n) that I can see would be:
A text value of type char(n) is always "n" characters in length. If you
assign
less than "n" characters, it is right-padded with spaces. In all other
respects it behaves as any other text type of length "n" with right-trailing
spaces.

[rant off - ah, feel better for that :-]

--
  Richard Huxton
  Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org

0
Reply jer 2/18/2004 6:06:32 PM

Hi,

in mysql I was able to make an insert such as:

INSERT INTO TABLE (integervariable) VALUES ('')

and have it either insert that variable, or insert the default if it had
been assigned.  In postgresql it gives and error every time that this is
attempted.  Since I have so many queries that do this on my site already, is
there any way to set up a table so that it just accepts this sort of query?

Thanks,
Jeremy


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

0
Reply jer 2/18/2004 7:25:19 PM

On Wed, 18 Feb 2004, Jeremy Smith wrote:

> Hi,
> 
> in mysql I was able to make an insert such as:
> 
> INSERT INTO TABLE (integervariable) VALUES ('')
> 
> and have it either insert that variable, or insert the default if it had
> been assigned.  In postgresql it gives and error every time that this is
> attempted.  Since I have so many queries that do this on my site already, is
> there any way to set up a table so that it just accepts this sort of query?

First off, the reason for this problem is that Postgresql adheres to the 
SQL standard while MySQL heads off on their own, making it up as they go 
along.  This causes many problems for people migrating from MySQL to 
almost ANY database.

Phew, now that that's out of the way, here's the standard ways of doing 
it.

Use DEFAULT:  If no default is it will insert a NULL, otherwise the 
default will be inserted:
insert into table (integervar) values (DEFAULT);  

OR

Leave it out of the list of vars to be inserted
insert into table (othervars, othervars2) values ('abc',123);

OR

Insert a NULL if that's what you want:

insert into table (integervar) values (NULL);

Note that NULL and DEFAULT are not quoted.


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org

0
Reply scott 2/18/2004 7:43:34 PM

So exactly what is the order of casts that produces
different results with:

	'x' || ' ' || 'x' and 'x' || ' '::char15 || 'x'

Are operators being invoked both (text,text)?

I'm trying to understand the precedence that causes
the different results.

elein

On Tue, Feb 17, 2004 at 10:53:17PM -0500, Tom Lane wrote:
> elein <elein@varlena.com> writes:
> > Apparently the ::char is cast to varchar and then text?
> 
> No, directly to text, because the || operator is defined as taking text
> inputs.  But there's no practical difference between text and varchar on
> this point.
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
>                http://archives.postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

0
Reply elein 2/18/2004 8:05:47 PM

Scott,

I understand that MySQL's adherence to the standards must be lazy as I am
running into frequent issues as I transfer my site.  Unfortunately I have
over 2500 queries, and many more of them needed to be rewritten than I ever
would have imagined.  I guess MySQL is the IE of open source DB, and
PostgreSQL is Netscape / Mozilla, in more ways than one.

I guess in some sense, since I relied on MySQL's laziness, my code also
became a bit lazy.  There are many locations where I accept user input from
a form, and then have a process page.  And on that process page I might have
hundreds of variables that look like:

$input = $_POST['input'];

and in the old days, if that was an empty value and inserted into a mysql
query, it would just revert to the default.  Now it looks like I need to:

$input = $_POST['input'];
if (!$input) {
    $input = DEFAULT;
}

over and over and over and over.... :)  I guess I am just looking for a
shortcut since the site conversion has already taken a week and counting,
when I originally was misguided enough to think it would take hours.

Anyway, the help on this list is much appreciated..

Jeremy

-----Original Message-----
From: scott.marlowe [mailto:scott.marlowe@ihs.com]
Sent: Wednesday, February 18, 2004 2:44 PM
To: Jeremy Smith
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Inserting NULL into Integer column


On Wed, 18 Feb 2004, Jeremy Smith wrote:

> Hi,
>
> in mysql I was able to make an insert such as:
>
> INSERT INTO TABLE (integervariable) VALUES ('')
>
> and have it either insert that variable, or insert the default if it had
> been assigned.  In postgresql it gives and error every time that this is
> attempted.  Since I have so many queries that do this on my site already,
is
> there any way to set up a table so that it just accepts this sort of
query?

First off, the reason for this problem is that Postgresql adheres to the
SQL standard while MySQL heads off on their own, making it up as they go
along.  This causes many problems for people migrating from MySQL to
almost ANY database.

Phew, now that that's out of the way, here's the standard ways of doing
it.

Use DEFAULT:  If no default is it will insert a NULL, otherwise the
default will be inserted:
insert into table (integervar) values (DEFAULT);

OR

Leave it out of the list of vars to be inserted
insert into table (othervars, othervars2) values ('abc',123);

OR

Insert a NULL if that's what you want:

insert into table (integervar) values (NULL);

Note that NULL and DEFAULT are not quoted.




---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

0
Reply jer 2/18/2004 8:15:43 PM

On Wed, 18 Feb 2004, Jeremy Smith wrote:

> Scott,
> 
> I understand that MySQL's adherence to the standards must be lazy as I am
> running into frequent issues as I transfer my site.  Unfortunately I have
> over 2500 queries, and many more of them needed to be rewritten than I ever
> would have imagined.  I guess MySQL is the IE of open source DB, and
> PostgreSQL is Netscape / Mozilla, in more ways than one.

Good comparison.

> I guess in some sense, since I relied on MySQL's laziness, my code also
> became a bit lazy.  There are many locations where I accept user input from
> a form, and then have a process page.  And on that process page I might have
> hundreds of variables that look like:
> 
> $input = $_POST['input'];
> 
> and in the old days, if that was an empty value and inserted into a mysql
> query, it would just revert to the default.  Now it looks like I need to:
> 
> $input = $_POST['input'];
> if (!$input) {
>     $input = DEFAULT;
> }

I've run into this kind of thing before.  IT helps if you have an array of 
all your fields like:

$fields = array("field1","field3","last_name");

and then you can foreach across the input:

foreach($fields as $f){
  if (!$_POST[$f]){
    $_POST[$f]='DEFAULT';
  } else {
    $_POST[$f] = "'".$_POST[$f]."'";
  }
}

> over and over and over and over.... :)  I guess I am just looking for a
> shortcut since the site conversion has already taken a week and counting,
> when I originally was misguided enough to think it would take hours.

Well, you might find yourself rewriting fair portions of your site, but 
usually you wind up with better code and better checking, so it's a bit of 
a trade off.

> Anyway, the help on this list is much appreciated..
> 
> Jeremy
> 
> -----Original Message-----
> From: scott.marlowe [mailto:scott.marlowe@ihs.com]
> Sent: Wednesday, February 18, 2004 2:44 PM
> To: Jeremy Smith
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] Inserting NULL into Integer column
> 
> 
> On Wed, 18 Feb 2004, Jeremy Smith wrote:
> 
> > Hi,
> >
> > in mysql I was able to make an insert such as:
> >
> > INSERT INTO TABLE (integervariable) VALUES ('')
> >
> > and have it either insert that variable, or insert the default if it had
> > been assigned.  In postgresql it gives and error every time that this is
> > attempted.  Since I have so many queries that do this on my site already,
> is
> > there any way to set up a table so that it just accepts this sort of
> query?
> 
> First off, the reason for this problem is that Postgresql adheres to the
> SQL standard while MySQL heads off on their own, making it up as they go
> along.  This causes many problems for people migrating from MySQL to
> almost ANY database.
> 
> Phew, now that that's out of the way, here's the standard ways of doing
> it.
> 
> Use DEFAULT:  If no default is it will insert a NULL, otherwise the
> default will be inserted:
> insert into table (integervar) values (DEFAULT);
> 
> OR
> 
> Leave it out of the list of vars to be inserted
> insert into table (othervars, othervars2) values ('abc',123);
> 
> OR
> 
> Insert a NULL if that's what you want:
> 
> insert into table (integervar) values (NULL);
> 
> Note that NULL and DEFAULT are not quoted.
> 
> 
> 
> 


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

0
Reply scott 2/18/2004 8:19:48 PM


I've run into this kind of thing before.  IT helps if you have an array of
all your fields like:

$fields = array("field1","field3","last_name");

and then you can foreach across the input:

foreach($fields as $f){
  if (!$_POST[$f]){
    $_POST[$f]='DEFAULT';
  } else {
    $_POST[$f] = "'".$_POST[$f]."'";
  }
}

Wow, great idea.  I will definitely do this, thanks alot.



Well, you might find yourself rewriting fair portions of your site, but
usually you wind up with better code and better checking, so it's a bit of
a trade off.


No doubt that this is true.  Of course even without the better code and
error checking, the extra features like stored procedures and automatic row
locking was more than enough to make the switch worth it.

Thanks again!

Jeremy


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faqs/FAQ.html

0
Reply jer 2/18/2004 8:43:13 PM

On Wed, 18 Feb 2004, Jeremy Smith wrote:

> 
> 
> I've run into this kind of thing before.  IT helps if you have an array of
> all your fields like:
> 
> $fields = array("field1","field3","last_name");
> 
> and then you can foreach across the input:
> 
> foreach($fields as $f){
>   if (!$_POST[$f]){
>     $_POST[$f]='DEFAULT';
>   } else {
>     $_POST[$f] = "'".$_POST[$f]."'";
>   }
> }
> 
> Wow, great idea.  I will definitely do this, thanks alot.
> 
> 
> 
> Well, you might find yourself rewriting fair portions of your site, but
> usually you wind up with better code and better checking, so it's a bit of
> a trade off.
> 
> 
> No doubt that this is true.  Of course even without the better code and
> error checking, the extra features like stored procedures and automatic row
> locking was more than enough to make the switch worth it.
> 
> Thanks again!

You're welcome!  Enjoy getting to know Postgresql and all the great folks 
on the lists, I know I have.


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to majordomo@postgresql.org so that your
      message can get through to the mailing list cleanly

0
Reply scott 2/18/2004 8:44:47 PM

> and then you can foreach across the input:
> 
> foreach($fields as $f){
>   if (!$_POST[$f]){
>     $_POST[$f]='DEFAULT';
>   } else {
>     $_POST[$f] = "'".$_POST[$f]."'";
>   }
> }

Default in quotes isn't going to work, and please tell me you escape
those things with pg_escape_string() at some point.


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faqs/FAQ.html

0
Reply pg 2/18/2004 8:53:55 PM

On Wed, 18 Feb 2004, Rod Taylor wrote:

> > and then you can foreach across the input:
> > 
> > foreach($fields as $f){
> >   if (!$_POST[$f]){
> >     $_POST[$f]='DEFAULT';
> >   } else {
> >     $_POST[$f] = "'".$_POST[$f]."'";
> >   }
> > }
> 
> Default in quotes isn't going to work, and please tell me you escape
> those things with pg_escape_string() at some point.

Note that the ' marks aren't part of the string, they are the delimiter of 
the string, and I always run every server with magic_quotes_gpc on.

anything else? :-)


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

0
Reply scott 2/18/2004 8:58:31 PM

> Note that the ' marks aren't part of the string, they are the delimiter of 
> the string, and I always run every server with magic_quotes_gpc on.
> 
> anything else? :-)

Good point. I looked at the single quotes of the second line and somehow
the DEFAULT got quoted as well ;)


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faqs/FAQ.html

0
Reply pg 2/18/2004 9:29:56 PM

On Wed, 18 Feb 2004, Rod Taylor wrote:

> > Note that the ' marks aren't part of the string, they are the delimiter of 
> > the string, and I always run every server with magic_quotes_gpc on.
> > 
> > anything else? :-)
> 
> Good point. I looked at the single quotes of the second line and somehow
> the DEFAULT got quoted as well ;)

Oh, and I'm stuck using add_slashes (or the magic_quotes_gpc thingie) 
'cause I'm on a server that's being eoled in favor of .net, and it's 
running PHP 4.0.6... ugh.  We really gotta get it upgraded soon.


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

0
Reply scott 2/18/2004 9:50:12 PM

elein <elein@varlena.com> writes:
> So exactly what is the order of casts that produces
> different results with:

> 	'x' || ' ' || 'x' and 'x' || ' '::char15 || 'x'

> Are operators being invoked both (text,text)?

The only relevant operator is "text || text" (there are also some ||
operators for arrays, bytea, and BIT, but these will all be discarded
as not the most plausible match).  Therefore, in your first example the
unspecified literals will all be presumed to be text, so the space does
not get trimmed.

One of the things we could think about as a way to tweak the behavior is
creating "||" variants that are declared to accept char(n) on one or
both sides.  These could actually use the same C implementation function
(textcat) of course.  But declaring them that way would suppress the
invocation of rtrim() as char-to-text conversion.

However, if we did that then "||" would behave differently from other
operators on character strings, so it doesn't seem like a very
attractive option to me.

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match

0
Reply tgl 2/19/2004 4:58:37 AM

I guess I am asking about the cast sequence from
char(n) to text.  
	('  '::char(n))::text trims spaces. This is wrong, imo.
	'     '::text does not trim spaces.
	'     '::char(n) does not trim spaces and pads.

char(n) should not trim spaces, right? And 
it doesn't on an insert.  Text does not trim spaces.
Somewhere the space trimming occurs.

If it is in the operator || then the operator is wrong.  
If char(n) is properly defined to not trim spaces then
there should be a separate cat for char(n).  It is correct
for it to behave differently than cat for text and varchar
because of the different trimming behaviour.

I can do this patch if there is agreement. But 
I may not be able to do it immediately.  

elein


On Wed, Feb 18, 2004 at 11:58:37PM -0500, Tom Lane wrote:
> elein <elein@varlena.com> writes:
> > So exactly what is the order of casts that produces
> > different results with:
> 
> > 	'x' || ' ' || 'x' and 'x' || ' '::char15 || 'x'
> 
> > Are operators being invoked both (text,text)?
> 
> The only relevant operator is "text || text" (there are also some ||
> operators for arrays, bytea, and BIT, but these will all be discarded
> as not the most plausible match).  Therefore, in your first example the
> unspecified literals will all be presumed to be text, so the space does
> not get trimmed.
> 
> One of the things we could think about as a way to tweak the behavior is
> creating "||" variants that are declared to accept char(n) on one or
> both sides.  These could actually use the same C implementation function
> (textcat) of course.  But declaring them that way would suppress the
> invocation of rtrim() as char-to-text conversion.
> 
> However, if we did that then "||" would behave differently from other
> operators on character strings, so it doesn't seem like a very
> attractive option to me.
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

0
Reply elein 2/19/2004 6:55:05 PM

elein <elein@varlena.com> writes:
> Somewhere the space trimming occurs.

The cast from char(n) to text (or varchar) is what's doing the trimming
in 7.4.  I think you can mostly revert the change by changing that
pg_cast entry to specify no conversion function instead of rtrim().
However that would probably result in squirrely, non-spec behavior for
comparisons.

> If char(n) is properly defined to not trim spaces then
> there should be a separate cat for char(n).

Possibly, but I think that is considering the issue much too narrowly.
Concatenation is not the only textual operator.

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match

0
Reply tgl 2/19/2004 8:06:21 PM
comp.databases.postgresql.sql 1306 articles. 0 followers. Post

41 Replies
32 Views

Similar Articles

[PageSpeed] 56


  • Permalink
  • submit to reddit
  • Email
  • Follow


Reply:

Similar Artilces:

Performance on 7.4 vs 7.2?
Hi there Pg admins, I have 2 systems, one OLD, (linux 2.4 running postgresql-7.2.1-5 with a perl web db on Apache/1.3.23 mod_perl 1.26 and dbi 1.37 with peristent db connections via apache:dbi. ) The other system is NEW (running the same web app on linux 2.6 postgresql-7.4.2-1 w/ Apache/2.0.49 & mod_perl/1.99_12 & apache:dbi. fedora core 2) Both systems have almost identical hardware, and have had the same tweaks made to pg - at least all I can rack out of my brain � their SYSV shared mem increased to 128mb shared_buffers = 15200 sort_mem = 32168 effective_ca...

performance problem aftrer update from 7.1 to 7.4.2
Hello everyone, i just upgraded my old postgres-database from version 7.1 to 7.4.2. i dumped out my 7.1 database (with pg_dump from 7.1) as an sql-file with copy-commands and to one file using insert-statements. after initalizing and starting postgres 7.4 on a different port and datadirectory, i tried to import the sql-dump with the copy statements. this import fails, but importing the dump-file with inserts took a long time but was successfully. ok, at first i do a vacuum and analyze and fire up the database again. now i changed my php-scripts to use the new database as dataso...

Oracle 7.3.4 performance issues on AIX 4.3.2
Hi Guys, We are using AIX4.3.2 with Oracle 7.3.4 on F50 configured Paging space is 512MB with 40% max utilization. Database is installed on SSA RAID box(hdisk1) where we have configured RAID5. I have noticed immense performance issues with server in peak hrs, as per my observation before we / user execute any search query on database system performs in reasonable condition ASA we execute any search query server encounter extreme bottleneck issues Below I have given output of sar vmstat and iostat commands before executing search query and after executing search query, please advice how can ...

Performance 7.3.4
This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01C45473.5CD8E20A Content-Type: text/plain; charset="iso-8859-1" We are trying to upgrade postgres 7.2 to postgres 7.3.4. We are having problems with some reports that have an excellent performance in 7.2 and now, in 7.3.4 the performance is too bad. Did someone have this problem? What should we do? ------_=_NextPart_001_01C45473.5CD8E20A Content-Type: text/html; charset="iso-8859-1" Co...

D3Linux 7.4 disk performance
Was D3Linux 7.4 suppose to improve disk performance ? Can someone explain the use of "raw partitions" in the "/usr/lib/pick/pick0" config below ? disk /dev/sda6 0 1959864 r /dev/raw/raw1 # disk 0 disk /dev/sda7 0 1959864 r /dev/raw/raw2 # disk 1 disk /dev/sda8 0 1959864 r /dev/raw/raw3 # disk 2 disk /dev/sda9 0 1959864 r /dev/raw/raw4 # disk 3 disk /dev/sda10 0 1959864 r /dev/raw/raw5 # disk 4 disk /dev/sda11 0 1959864 r /dev/raw/raw6 # disk 5 Bob I haven't got to play with 7.4 yet but I played a part it determining the performance issue. Basicall...

Slow Performance with 7.4.1
Hi, I just installed pg 7.4.1 on a new system P4, 3.2Gh HT, 1GB Mem, RH9 and discovered that some of the processes have become extremely slow. Mainly I am talking about a SELECT COUNT(*) or VACUUM. Before running the same database on a P4. 2Ghz 412MB mem, I got the job done in some cases half the time. The weird thing however is, that normal inserts seem to be much faster on the new system and I am wondering if it is Postgres or HW related. Does anyone has an advise ? Thanks Alex ---------------------------(end of broadcast)--------------------------- TIP 4: Don&#...

tweaking MemSet() performance
HI, I'm using 7.4.5 on Mac OS X (G5) and was profiling it to see why it is SO SLOW at committing inserts and deletes into a large database. One of the many slowdowns was from MemSet. I found an old (2002) thread about this and retried the tests (see below). The main point is that the system memset crushes pg's!! Is it possible to add a define to call the system memset at build time! This probably isn't the case on other systems. I wanted to know the size of FunctionCallInfoData (in execQual.c) because the profiler said that if it was over 128 then use the syst...

Oracle 8.1.7.4 Performance Issues
Hi, I have a question about Oracle 8.1.7.4 performance, where I need to find the answer or solution. We have a Oracle 8.1.7.4 database installed on a SUN Enterprise 450 with 2 processors and 2 GB RAM. The shared_pool is approx. 150 MB in size. The db_block_buffer are in total 1,2 GB in size. When I run the regular queries on the database, the took approx. 0,05 to 0,4 seconds for each query. For my tests, I run the query frequently on the database and they took between 0,05 and 0,4 sec. That fine for me. In the next morning, I ran the same queries and the first say 20 queries are totally s...

pg 7.4.rc1, Range query performance
Postgresql 7.4.rc1 on i686 pc linux compiled by gcc 2.06. All configuration settings are default. Hi, Trying to find a way to improve range query performance. The table Test has about 30 million records. -- DLong, Dtimestamp, Dint, etc are domains of the respective types. create table Test ( id DLong not null, a Dtimestamp null, b Dint not null, c Dint not null, d Dstring null, constraint PK_id primary key (id), constraint AK_abc u...

Postgres v.7.3.4
I need to provide recommendations for optimal value for the shared_buffers. This has to do with some optional feature we have in our application. If turned on - a number of extra tables involved in the operations. I don't have an experience with PG perf. testing and I guess that I may need to account for the size of the tables, perhaps the size of index tables as well..? Any hints, or pointers to related reading would be very appreciated. Mike. ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend On Thursday 15 ...

Horrendous Performance on AIX w/ D3 7.4.2
Has anyone else done a migration after running for a long time on an older D3 release (say 7.1.0) including an upgrade to new hardware and noticed "disappointing" performance? I have a client that is going from an old 43P RS6000 machine (332 MHz processor, 768MB memory of which 500MB allocated to D3) to a brand new 51A machine with dual 2.1GHz processors and 8 GB of memory of which 2GB are allocated to D3). Imagine our surprise when our initial test (counting a file containing roughly 5.6 million records) actually ran twice as long as on the old machine! The s...

sysstat-4.1.7
Hi all, Good news: sysstat release 4.1.7 is now available for all of you Linux users. Sysstat 4.1.7 is still a development version but it should be considered as a possible release candidate for the next sysstat 5.0.0 stable release! It is available for download from my web page at: http://perso.wanadoo.fr/sebastien.godard/ It is also available on ibiblio's Linux archive site: ftp://ibiblio.org/pub/Linux/system/status/ 109kB sysstat-4.1.7.tar.gz 89kB sysstat-4.1.7.tar.bz2 111kB sysstat-4.1.7-1.src.rpm 61kB sysstat-4.1.7-1.i386.rpm The sysstat utilities are a collection ...

FAQ 4.8 How do I perform an operation on a series of integers? #7 263353
This is an excerpt from the latest version perlfaq4.pod, which comes with the standard Perl distribution. These postings aim to reduce the number of repeated questions as well as allow the community to review and update the answers. The latest version of the complete perlfaq is at http://faq.perl.org . -------------------------------------------------------------------- 4.8: How do I perform an operation on a series of integers? To call a function on each element in an array, and collect the results, use: @results = map { my_func($_) } @array; For e...

FAQ 4.8 How do I perform an operation on a series of integers? #7 555189
This is an excerpt from the latest version perlfaq4.pod, which comes with the standard Perl distribution. These postings aim to reduce the number of repeated questions as well as allow the community to review and update the answers. The latest version of the complete perlfaq is at http://faq.perl.org . -------------------------------------------------------------------- 4.8: How do I perform an operation on a series of integers? To call a function on each element in an array, and collect the results, use: @results = map { my_func($_) } @array; For example: ...

Performance with IDS 7.31.UD2 on Linux (2.4 kernel)
Hello I am testing linux to migrate some informix IDS73 servers (currently windows and SCO OSR5 environnement) My linux informix that I got is a 7.31.UD2 version and I think this version is very slow...slower than the SCO or windows 7.31 versions for the same hardware and the same onconfig parameters. Is there another 7.31 version (UD3 or more) ? I can't find any answer on the ibm/informix website... Do you know where can I download it for evaluation (free, I mean...) ? Thank you for your answers jba quanrante.deux wrote: > Hello > > I am test...

AIX 4.3.3 Informix 7.13 Performance Problem
I have an IBM RS6000 running AIX 4.3.3.0. On this system I have an Informix Database. The software issues are :- Informix Standard Engine 7:13 UC2 and Informix SQL 6:03 UC1 The system performance is poorer than I would expect for this hardware. Are there any known issues running this software on AIX 4.3.3? I would be very grateful for any help on this subject. informix@terrykay.co.uk wrote: > I have an IBM RS6000 running AIX 4.3.3.0. > On this system I have an Informix Database. The software issues are :- > > Informix Standard Engine 7:13 UC2 and > Inform...

When Performing A Database Import On 8.1.7.4 I Get Ora-6550
Hi I am importing a 8.1.6 dump file into a 8.1.7.4 database, I have created the users, assigned them there tablespaces etc and created a huge rollback seg. When i start importing I get the following message below, Oracle informed me it was a bug at 8.1.6 and should not be effected in a 8.1.7.4 database. Have tried several things, my last thing to try will be upgrade the 8.1.6 db to 8.1.7.4 and export and import again into my new database. Anyones ideas are very much appricated! Iain Connected to: Oracle8i Release 8.1.7.4.1 - Production JServer Release 8.1.7.4.1 - Production Export file ...

Re: one Performance question AIX 4.3, informix 7.31ud4
REBELLO, Rulesh Felix said: > we have a 2 CPU, 1GB mem, informix 7.31ud4, AIX 4.3 machine hosting 3 > production databases with following sizes > db1 - 58 GB > db2 - 6 GB > db3 - 2 GB > > At the moment all the 3 databases are in the same instance. > CPU utilization is on an average 75 % over the course of the day. > > we are planning on separating the above databases in 2 seperate instances > without upgrading the hardware resources. > > instance 1 - db1 > instance 2 - db2 and db3 > > just need to find out from the group wh...

OS X 10.4, Matlab 7.1.0.183 Poor Performance
Hi, I have been away from Matlab for several months. In betwixt the operating system of my computer ( now Mac OS X 10.4.4) was upgraded as well as the version of Matlab (now 7.1.0.183 (R14) Service Pack 3). Some bad things have happened. I have a file (dump.fil) of floating point numbers. It is 11050 lines long and seven columns wide. When I do "load dump.fil", from the Matlab command line, it takes 8 minutes and 12 seconds to complete the operation! The machine has no other significant load. My colleague on a Linux box next door loads the same file almost instantaneously. What on...

RE: one Performance question AIX 4.3, informix 7.31ud4 #2
Rulesh Felix wrote (in HTML) > we have a 2 CPU, 1GB mem, informix 7.31ud4, AIX 4.3 machine hosting 3 production databases >with following sizes > db1 - 58 GB > db2 - 6 GB > db3 - 2 GB > At the moment all the 3 databases are in the same instance. > CPU utilization is on an average 75 % over the course of the day. > we are planning on separating the above databases in 2 seperate instances without upgrading the hardware resources. > instance 1 - db1 > instance 2 - db2 and db3 > just need to find out from the group whet...

Faster performance when using where cust_id = '123' vs cust_id = 123. Does 7.4 fix this??
We have got used to the problem that queries of the format: select * from customer where cust_id = '123' are much much faster than select * from customer where cust_id = 123 (where cust_id is defined as bigint). a. Why is this. b. Will moving to v7.4 change this so we can avoid the whole '123' casting thing. Tx folks, D ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message ca...

GnuPG / PGP signed checksums for PostgreSQL 7.4.5, 7.4.4, 7.3.7, 7.3.6, 7.3.5. 7.2.5
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 This is a PGP-signed copy of the checksums for following PostgreSQL versions: 7.4.5 7.4.4 7.3.7 7.3.6 7.3.5 7.2.5 The latest copy of the checksums for these and other versions, as well as information on how to verify the files you download for yourself, can be found at: http://www.gtsm.com/postgres_sigs.html ## Created with md5sum: 97e750c8e69c208b75b6efedc5a36efb postgresql-7.4.5.tar.bz2 bffc3fe775c885489f9071e97f43ab9b postgresql-base-7.4.5.tar.bz2 548a73c898e65f901dbc06d622a2bc63 postgresql-docs-7.4.5.tar.b...

Coming soon: PG 7.4.4, 7.3.7, 7.2.5
The core committee has agreed that this bug http://archives.postgresql.org/pgsql-hackers/2004-08/msg00639.php is serious enough that we'd better push out update releases for all the affected versions. I want to hold off a day or so and see if a couple of currently-open bug reports can be resolved, but it's going to happen soon. If anyone has any back-patches that they've been thinking of getting in, now would be a good time. BTW, I realized this morning that 7.1.* also has a version of the bug, because it too writes and flushes an XLOG COMMIT record before it does anyt...

[ciao-users] ACE+TAO+CIAO
Hi, We are pleased to announce a new beta of ACE-5.4.7, TAO-1.4.7 and CIAO-0.4.7. Since the x.4.6 beta had so many changes and improvements, we wanted to get this new beta out quickly to fix several important bugs reported by our users, which we greatly appreciate, as always. We've also added a number of subsetting improvements that reduce the static/dynamic footprint of ACE+TAO significantly. As usual the latest release is available at: http://deuce.doc.wustl.edu/Download.html The doxygen documentation for the beta is also available. We encourage you to download the ...

pgsql-server: Preliminary release notes for 7.4.4, 7.3.7, 7.2.5.
Log Message: ----------- Preliminary release notes for 7.4.4, 7.3.7, 7.2.5. Will add to the back branches later. Modified Files: -------------- pgsql-server/doc/src/sgml: release.sgml (r1.281 -> r1.282) (http://developer.postgresql.org/cvsweb.cgi/pgsql-server/doc/src/sgml/release.sgml.diff?r1=1.281&r2=1.282) ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ...