Thread: cannot get CREATE TABLE AS to work
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
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
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/
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
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
"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
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
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); ]
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); > ] >
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...