Thread: cannot get CREATE TABLE AS to work

cannot get CREATE TABLE AS to work

From
"Creager, Robert S"
Date:
I'm sure I'm doing something wrong, and I'm hoping someone can show me the
way of things.  Running 7.1beta5 on an Ultra 5, Solaris 2.6 w/256Mb mem.  If
I remove the AS, the table creates correctly and I can do the INSERT INTO
with the SELECT clause

psql -d tassiv -c "\
create table observationsII ( \
ra float8 not null, \
decl float8 not null, \
mag float8 not null, \
smag float8 not null, \
obs_id serial, \
file_id int4 references files on delete cascade, \
star_id int4 references comp_loc on delete set null default null ) \
AS select o.ra, o.decl, o.mag, o.smag, o.file_id from
observations o"

ERROR:  parser: parse error at or near "AS"

And the next question, should this really be taking 3 hours to insert 315446
records?  I noticed the disk is basically idle during the few times when I
watched.  Would this be because of the index created on obs_id?

time psql -d tassiv -c "insert into observationsII( ra, decl, mag, smag,
file_id ) select ra, decl, mag, smag, file_id from observations"
INSERT 0 315446
0.01u 0.01s 3:13:22.39 0.0%


Robert Creager
Senior Software Engineer
Client Server Library
303.673.2365 V
303.661.5379 F
888.912.4458 P
StorageTek
INFORMATION made POWERFUL




Re: cannot get CREATE TABLE AS to work

From
"Josh Berkus"
Date:
Robert,

> How then can I add in a DEFAULT nextval in place of SERIAL and get
> the
> REFERENCES in there?  Or can I?

You can't (as far as I know).   If that's important to you, you need to
create the table first with a regular CREATE TABLE statement, then do
INSERT INTO.  CREATE TABLE AS is, I believe, primarily for temporary
tables.

It's also a good idea to stay away from CREATE TABLE AS in applications
that might have to be portable; this is one statment whose syntax varies
widely from platform to platform.  For example, Transact-SQL (MS) uses
SELECT ... INTO instead of CREATE TABLE AS.

-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________                                      Josh Berkus Complete
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco
 


Re: cannot get CREATE TABLE AS to work

From
Peter Eisentraut
Date:
Creager, Robert S writes:

> psql -d tassiv -c "\
> create table observationsII ( \
> ra float8 not null, \
> decl float8 not null, \
> mag float8 not null, \
> smag float8 not null, \
> obs_id serial, \
> file_id int4 references files on delete cascade, \
> star_id int4 references comp_loc on delete set null default null ) \
> AS select o.ra, o.decl, o.mag, o.smag, o.file_id from
> observations o"
>
> ERROR:  parser: parse error at or near "AS"

Either you have an AS clause, or a column list, not both.

> And the next question, should this really be taking 3 hours to insert 315446
> records?  I noticed the disk is basically idle during the few times when I
> watched.  Would this be because of the index created on obs_id?

Quite likely.  Also make sure you wrap the inserts into a BEGIN/COMMIT
block.

-- 
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/



Re: cannot get CREATE TABLE AS to work

From
"Josh Berkus"
Date:
Robert,
I can't help you with your performance problem, but I can help you with
CREATE TABLE AS.  You've mistaken the syntax; CREATE TABLE AS does not
use column definitions other than the query.  Thus, the correct syntax
should be:


> create table observationsII
> AS select o.ra, o.decl, o.mag, o.smag, o.file_id from
> observations o;


-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________                                      Josh Berkus Complete
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco
 


Re: cannot get CREATE TABLE AS to work

From
Josh Berkus
Date:
Robert,

> Thanks for the pointers.  I'm actually working on modifying the structure of
> an existing db, so this is all within Pg.  Those INSERT INTOs with SELECTs
> are painfully slow, and I have an larger table to do this to...  I guess
> Perl will have to rescue me...

Why don't you post your SQL statements, table definitions, and estimate
results?  I'm sure one or more of the performance experts (Tom Stephan)
could find some improvements.  It doesn't makes sense that CREATE TABLE
AS should be faster than INSERT INTO ... SELECT.  It should be *slower*
unless you have 2 dozen indexes on the target table.
                -Josh Berkus

-- 
______AGLIO DATABASE SOLUTIONS___________________________                                       Josh Berkus  Complete
informationtechnology      josh@agliodbs.com   and data management solutions       (415) 565-7293  for law firms, small
businesses      fax  621-2533   and non-profit organizations.       San Francisco
 


Re: cannot get CREATE TABLE AS to work

From
Tom Lane
Date:
"Creager, Robert S" <CreagRS@LOUISVILLE.STORTEK.COM> writes:
> And the next question, should this really be taking 3 hours to insert 315446
> records?  I noticed the disk is basically idle during the few times when I
> watched.  Would this be because of the index created on obs_id?

Not for a single index.  I can believe that the referential integrity
checks you're requiring on file_id and star_id might take that much
time, however.
        regards, tom lane


Re: cannot get CREATE TABLE AS to work

From
Josh Berkus
Date:
Robert,

> I suspect that the INSERT INTO SELECT in this case will take longer than a
> CREATE TABLE AS because of the referential integrity check needed on every
> INSERT (per Tom Lane).

In that case, what about:

a) dropping the referential integrity check;
2) making the referential integrity check deferrable (there's a way to
do this, it was discussed a couple weeks ago - ask Tom).

-Josh Berkus


-- 
______AGLIO DATABASE SOLUTIONS___________________________                                       Josh Berkus  Complete
informationtechnology      josh@agliodbs.com   and data management solutions       (415) 565-7293  for law firms, small
businesses      fax  621-2533   and non-profit organizations.       San Francisco
 


Re: cannot get CREATE TABLE AS to work

From
Stephan Szabo
Date:
On Fri, 9 Mar 2001, Josh Berkus wrote:

> Robert,
> 
> > I suspect that the INSERT INTO SELECT in this case will take longer than a
> > CREATE TABLE AS because of the referential integrity check needed on every
> > INSERT (per Tom Lane).
> 
> In that case, what about:
> 
> a) dropping the referential integrity check;

Unfortunately if he adds it back in with ALTER TABLE, that's going to be
slow as well.  I did it in a fashion I felt was cleaner code, but in
practice, I think the implementation's performance is poor enough that 
it might be worth doing in the less clean way (running a single select
looking for failing rows when possible on alter table rather than checking
each row -- less clean because it means keeping information on what the
fk check is in multiple places. :( )

> 2) making the referential integrity check deferrable (there's a way to
> do this, it was discussed a couple weeks ago - ask Tom).

Well, you can always add deferrable initially immediate to the constraint
and run a SET CONSTRAINTS ALL DEFERRED, but I'm not sure that'll actually
be much faster, it still does a check per row I believe.

It's hacky, but I'd say, if you don't have other triggers you care about, 
twiddle pg_class.reltriggers for the class to 0, do the insert, set it
back to what it was before and then run selects to make sure the data is
valid (ie, would the constraint have failed).

[
assuming one column, something like:

select * from fktable where not exists(select * from pktable where pktable.pkcol=fktable.fkcol);
]



RE: cannot get CREATE TABLE AS to work

From
"Creager, Robert S"
Date:
Well, that explains why I wasn't seeing any appreciable speed increase with
the INITIALLY DEFERRED.  I tried mucking in pg_class, and saw a 3 fold
increase in insert speed on inserts into my table with 2 relational
triggers.  SET CONSTRAINTS ALL DEFERRED does nothing to very little to
increase the insertion speed.  15min 'INITIALLY DEFERRED' vs 13min 'ALL
DEFERRED' vs 5min 'pg_class update'.  And that 15 vs 13 could be machine
activity.

I'll appologize to anyone trying to follow this one sided converstion, as I
wasn't paying attention to the e-mail headers and though Josh was replying
to me only.

Cheers,
Rob

> -----Original Message-----
> 
> Well, you can always add deferrable initially immediate to 
> the constraint
> and run a SET CONSTRAINTS ALL DEFERRED, but I'm not sure 
> that'll actually
> be much faster, it still does a check per row I believe.
> 
> It's hacky, but I'd say, if you don't have other triggers you 
> care about, 
> twiddle pg_class.reltriggers for the class to 0, do the insert, set it
> back to what it was before and then run selects to make sure 
> the data is
> valid (ie, would the constraint have failed).
> 
> [
> assuming one column, something like:
> 
> select * from fktable where not exists
>  (select * from pktable where pktable.pkcol=fktable.fkcol);
> ]
> 


RE: cannot get CREATE TABLE AS to work

From
Stephan Szabo
Date:
On Fri, 9 Mar 2001, Creager, Robert S wrote:

> 
> Well, that explains why I wasn't seeing any appreciable speed increase with
> the INITIALLY DEFERRED.  I tried mucking in pg_class, and saw a 3 fold
> increase in insert speed on inserts into my table with 2 relational
> triggers.  SET CONSTRAINTS ALL DEFERRED does nothing to very little to
> increase the insertion speed.  15min 'INITIALLY DEFERRED' vs 13min 'ALL
> DEFERRED' vs 5min 'pg_class update'.  And that 15 vs 13 could be machine
> activity.

Yeah, theoretically if we could do something where it knew that there were
alot of them and tried to fall back to doing a single big check rather
than lots of little ones we'd get a performance increase, but I can't
really think of a good way to do that with what we have right now...