Thread: How do I save my tables?

An errors-only log is useless for this kind of analysis.  its the successful
statements that truly matter (in combination with the errors).  You should
log all statements during our next session so a complete log is available
for review.  You should also annotate said log with your thoughts/comments
before posting it (and trim out the errors that you subsequently corrected).

You do not "save" tables.  If you issue a successful CREATE TABLE statement
that table will be permanent.

You need replicate what you did "yesterday" and document what you did in
much more detail if you want someone to try and figure out what went wrong.
So go in, create one table.  Insert and query it.  Close out pgAdmin and
reboot if you like.  Then go back in and see if the table is still there.
If not note any changes to the pgAdmin dialogs hat your made while adding
said table.  My on.y thought is that you created temporary tables (which go
away at session end).

David J.




--
View this message in context: http://postgresql.1045698.n5.nabble.com/How-do-I-save-my-tables-tp5771217p5771228.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

David Johnston, 17.09.2013 14:59:
> You do not "save" tables.  If you issue a successful CREATE TABLE statement
> that table will be permanent.

Unless you disabled auto-commit and forgot to commit the CREATE TABLE statement...

Kimberly Israel, 17.09.2013 14:33:
> Yesterday I created several tables and was able to run queries that
> combined data from more than one table at a time, but when I reopened
> pgAdmin III today, the database I had created was there, but the
> tables were all gone.  I tried to save my work, but what got saved
> was a .sql file that seems to only contain the code for creating the
> database.  Basically what I need to know is how do I put my tables
> somewhere where I'll be able to continue working with them after a
> period of having the computer off?

This all sounds like you forgot to commit your changes.

When you have auto-commit disabled you need to commit every change you make to the database - including a CREATE TABLE
statement.

Thomas Kellerer wrote
> Kimberly Israel, 17.09.2013 14:33:
>> Yesterday I created several tables and was able to run queries that
>> combined data from more than one table at a time, but when I reopened
>> pgAdmin III today, the database I had created was there, but the
>> tables were all gone.  I tried to save my work, but what got saved
>> was a .sql file that seems to only contain the code for creating the
>> database.  Basically what I need to know is how do I put my tables
>> somewhere where I'll be able to continue working with them after a
>> period of having the computer off?
>
> This all sounds like you forgot to commit your changes.
>
> When you have auto-commit disabled you need to commit every change you
> make to the database - including a CREATE TABLE statement.

All true; and while I didn't actively ponder this scenario I find it
unlikely given the log file.  Given all of the syntax errors that we saw I
would have expected to see a large number of  (paraphrased) "current
transaction is aborted; ignoring all statements until Rollback has been
issued" messages.

Unless the OP was re-creating the table every time a typo occurred those
tables had to have been committed at some point during the session.

Also, I do not think the OP likely turned off auto-commit and so if that is
the default mode I would presume that said default is still in effect.

The temporary table thought is pretty much just as improbable but nothing
else readily comes to mind.

David J.




--
View this message in context: http://postgresql.1045698.n5.nabble.com/How-do-I-save-my-tables-tp5771217p5771257.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

Kimberly Israel wrote
> Thanks for your advice everyone - I'm  getting ready to try it again and
> keep detailed track of the steps, but I noticed under Options->Query
> tool->Query editor that "Enable Auto ROLLBACK" is checked by default.  Is
> this the opposite of auto-commit?  Would unchecking it then put me into
> auto-commit?

Please follow list-norms (and others behavior) and bottom-post.

"Enable Auto ROLLBACK" only makes sense if you are in transaction (not
auto-commit) mode.  It makes it so that "transaction is aborted...." message
does not appear since as soon as you have an error the software issues a
ROLLBACK for you.

Still, unless you are constantly re-creating the tables in question whenever
you cause an error those tables should have been committed at some point.

I also do not know if that option is independent of the option to use
auto-commit...

David J.



--
View this message in context: http://postgresql.1045698.n5.nabble.com/How-do-I-save-my-tables-tp5771217p5771267.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

Kimberly Israel wrote
>>An errors-only log is useless for this kind of analysis.  its the
successful
>
>>statements that truly matter (in combination with the errors).  You should
>>log all statements during our next session so a complete log is available
>>for review.  You should also annotate said log with your thoughts/comments
>>before posting it (and trim out the errors that you subsequently
corrected).
>
>>You do not "save" tables.  If you issue a successful CREATE TABLE
statement
>>that table will be permanent.
>
>>You need replicate what you did "yesterday" and document what you did in
>>much more detail if you want someone to try and figure out what went
wrong.
>>So go in, create one table.  Insert and query it.  Close out pgAdmin and
>>reboot if you like.  Then go back in and see if the table is still there.
>>If not note any changes to the pgAdmin dialogs hat your made while adding
>>said table.  My on.y thought is that you created temporary tables (which
go
>>away at session end).
>
>>David J.
>
> Is this what you mean by bottom-posting? With the arrows marking out the
> previous post?  I tried again and here's what happened:
>
> Opened pgAdmin III, double clicked on PostgreSQL 9.3 (x86) (localhost:
> 5432)
> Double clicked on database postgres, opened SQL query window
>
> CREATE DATABASE firedata; 
> -> Query returned successfully with no result in 3119 ms.
> --Didn't see anything, went to Options and unchecked "Enable auto
> rollback", refreshed object browser and database was there.

transactions are turned on; "CREATE DATABASE firedata" created the database
but you cannot see it in the browser until you "commit".  Unchecking
"enable..." seems to have caused a commit for the CREATE DATABASE command
since you were able to connect to "firedata" down further.


> CREATE TABLE landform (
> landform_code smallint,
> landform_type varchar(15)
> );
> -> Query returned successfully with no result in 31 ms.
> --Didn't see any sign of the table in the object browser, even after
> refreshing.

Created the table "landform" in the "postgres" database - NOT firedata!!!!
Since the object browser uses a different session to query the catalogs the
table will not appear until you commit the "CREATE TABLE" transaction in
your interactive session.


> INSERT INTO landform VALUES
> (1, 'ridgetop'),
> (2, 'open slope'),
> (3, 'spur ridge'),
> (4, 'gap'),
> (5, 'knob'),
> (6, 'plunging cove'),
> (7, 'bottom'),
> (8, 'bench');
> ->Query returned successfully: 8 rows affected, 11 ms execution time.
> --Still no sense of where the table actually is
>
> SELECT * FROM landform;
> -> returns the table with the values I'd entered

The above is still done in "postgres"; you can see all prior work that you
performed in the same transaction so you can see the table here even though
it is not yet been committed to permanent storage.


> Clicked "save file" from the toolbar and saved as landformtest.

All you are saving here is the working query (the raw text content) in the
pgAdmin interface; this has nothing to do with telling the server to save
your transactional work.


> Reopened pgAmin III, double clicked on firedata database.
>
> SELECT * FROM landform;
> ->ERROR:  relation "landform" does not exist
> LINE 1: SELECT * FROM landform;
>                       ^

In the "firedata" database now, not "postgres", so the table you created in
"postgres" would not appear here even if you had committed.


> Double clicked on postgres database in case the table somehow wound up
> there and tried to select from the table again, with the same result.

Did not "commit" the CREATE TABLE transaction so the table you created and
inserted into in "postgres" was not committed to permanent storage.

David J.






--
View this message in context: http://postgresql.1045698.n5.nabble.com/How-do-I-save-my-tables-tp5771217p5771279.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.