On 09/19/2012 07:54 PM, Marc Mamin wrote:
hello,
I've found a small logical issue while writing a migration script:
create table vtest (foo int);
create view vtest_v as select * from vtest;
drop table if exists vtest_v;
ERROR: "vtest_v" is not a table
drop view if exist
hello,
I've found a small logical issue while writing a migration script:
create table vtest (foo int);
create view vtest_v as select * from vtest;
drop table if exists vtest_v;
ERROR: "vtest_v" is not a table
drop view if exists vtest;
ERROR: "vtest" is not a vi
On Tue, 28 Jun 2011, Rick Genter wrote:
After issuing the \d you are still in the middle of your command. Witness
the following copy/paste of a terminal session:
Ah, so! I didn't see this.
Thank you very much,
Rich
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To m
postgres=# drop table foo
postgres-# \d
specifically note the prompt. the -# means you're in the middle of a
command. =# means its ready for a new command.
as another example...
pierce=# create table foo (id integer);
CREATE TABLE
pierce=# drop
pierce-# table
p
After issuing the \d you are still in the middle of your command. Witness
the following copy/paste of a terminal session:
bash-3.2$ ./psql
Password:
psql (8.4.4)
Type "help" for help.
postgres=# create table foo (bar int);
CREATE TABLE
postgres=# drop table foo
postgres-# \d
On Tue, 28 Jun 2011, Rick Genter wrote:
Silly question, but did you try it with a semicolon after the drop table?
Rick,
See my answer to Andy: that's incorrect syntax and psql complains.
I've noticed that if you are in the middle of a statement and issue a \
command, psql ignor
On Wed, 29 Jun 2011, Andy Firel wrote:
it might be sufficient to add a semicolon to your drop statement:
# drop table station_type;
Andy,
Actually, that's not true. On a whim I tried that and psql complained
about a syntax error at the initial 'd'.
Rich
--
Sent via pgsql-
Hi Rich,
it might be sufficient to add a semicolon to your drop statement:
# drop table station_type;
HTH,
Andy
- Ursprüngliche Mail -
> I cannot recall issuing a DROP TABLE command from psql that did not
> work,
> but seem to have this as a new experience.
>
> Whe
Silly question, but did you try it with a semicolon after the drop table?
# drop table station_type;
I've noticed that if you are in the middle of a statement and issue a \
command, psql ignores the SQL you've typed in and just does the \ command.
On Tue, Jun 28, 2011 at 3:34 PM, Ri
I cannot recall issuing a DROP TABLE command from psql that did not work,
but seem to have this as a new experience.
When I look at the database table list with '\d' I see
public | station_type | table| rshepard
public | station_type_s
On Sunday 07 March 2010 02.49:29 Bruce Momjian wrote:
> Manlio Perillo wrote:
> > I think the following behaviour is not intuitive:
> >
> > manlio=> DROP TABLE IF EXISTS foo.bar;
> > ERROR: schema "foo" does not exist
> >
> > The statement s
Manlio Perillo wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Hi.
>
> I think the following behaviour is not intuitive:
>
> manlio=> DROP TABLE IF EXISTS foo.bar;
> ERROR: schema "foo" does not exist
>
> The statement should not fai
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi.
I think the following behaviour is not intuitive:
manlio=> DROP TABLE IF EXISTS foo.bar;
ERROR: schema "foo" does not exist
The statement should not fail if the schema does not exist
Thanks Manlio
-BEGIN PGP SIGNATURE
Scott Marlowe writes:
> We've run into issues with drop table and slony as well. What version
> of slony are you running? We're running 1.2.14. Latest version in
> that branch in 1.2.16, but we haven't had cause to upgrade to it just
> yet. I'll be looking a
rrors
>
> But when i try drop table from slave i got very strange error:
We've run into issues with drop table and slony as well. What version
of slony are you running? We're running 1.2.14. Latest version in
that branch in 1.2.16, but we haven't had cause to upgrade to it
Maxim Boguk writes:
> Server was slave in slony replication.
> Now problem:
> table was unsubscribed from replication (without any errors)
> and then dropped from master without any errors
> But when i try drop table from slave i got very strange error:
> hh=# drop TABLE
DB version: PostgreSQL 8.3.6 (under linux)
no server/db crashes happen before.
Server was slave in slony replication.
Now problem:
table was unsubscribed from replication (without any errors)
and then dropped from master without any errors
But when i try drop table from slave i got very
On Fri, 2009-05-15 at 09:09 +0200, Luca Ferrari wrote:
> I'm just curious to know why after a drop table the disk file is emptied but
> still existent. What is the reason why the file is not deleted immediately?
To avoid various problems the files are removed after the next
Luca Ferrari writes:
> I'm just curious to know why after a drop table the disk file is emptied but
> still existent. What is the reason why the file is not deleted immediately?
It's protecting against some obscure race condition involving
reassignment of the relfilenode numbe
Hi,
I'm just curious to know why after a drop table the disk file is emptied but
still existent. What is the reason why the file is not deleted immediately?
Thanks,
Luca
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
On Apr 22, 2009, at 8:04 AM, Thomas Finneid wrote:
Alvaro Herrera wrote:
Try TRUNCATE. That leaves the less garbage behind and takes the less
time.
A follow up question, as far as I understand it, delete removes
entries in the fsm, so vacuum has to clean it all up when performing
a delet
Alvaro Herrera wrote:
Try TRUNCATE. That leaves the less garbage behind and takes the less
time.
A follow up question, as far as I understand it, delete removes entries
in the fsm, so vacuum has to clean it all up when performing a delete,
is this approximately correct? what happens with tru
On Tue, 2009-04-21 at 14:30 -0700, Christophe wrote:
> Indeed so, and I understand that part. But since Session1 didn't try
> to access 'bar', it can't distinguish that sequence from:
>
> Session2:
> BEGIN;
> TRUNCATE bar;
> COMMIT;
>
> Session1:
> BEGIN TRANSACTION ISOLATION LEVEL SER
Christophe writes:
> On Apr 21, 2009, at 2:15 PM, Jeff Davis wrote:
>> In Session1, the serializable transaction sees an empty version of
>> bar, even though it had tuples in at the time Session1 got its serializable
>> snapshot.
> Indeed so, and I understand that part. But since Session1 didn
On 21 Kwi, 21:30, alvhe...@commandprompt.com (Alvaro Herrera) wrote:
> marek.patr...@gmail.com escribió:
>
> > I was wondering if dropping a table is more efficient in PostgreSQL
> > 8.x in comparison to deleting it's content ?
>
> "8.x" is a meaningless version number in Postgres. Major versions
the time Session1 got its serializable
> snapshot.
Exactly.
> If Session2 does a DROP TABLE instead of TRUNCATE, Session1 will get an
> error when it tries to read "bar".
Actually, the scenario that I suppose the OP had in mind was to drop
and immediately recreate "bar&qu
On Apr 21, 2009, at 2:15 PM, Jeff Davis wrote:
In Session1, the serializable transaction sees an empty version of
bar,
even though it had tuples in at the time Session1 got its serializable
snapshot.
Indeed so, and I understand that part. But since Session1 didn't try
to access 'bar', it
E;
SELECT * FROM foo;
Session2:
BEGIN;
TRUNCATE bar;
COMMIT;
Session1:
SELECT * from bar;
COMMIT;
In Session1, the serializable transaction sees an empty version of bar,
even though it had tuples in at the time Session1 got its serializable
snapshot.
If Session2 does a DROP TABLE instead
On Apr 21, 2009, at 1:36 PM, Tom Lane wrote:
I was thinking of MVCC semantics, which is a different issue.
Indeed so, my error. This is a bit of a drift off-topic, but
rereading the docs, I'm now having trouble visualizing the real-world
effect of the non-MVCC-safeness of TRUNCATE. A tra
x...@thebuild.com (Christophe) writes:
> On Apr 21, 2009, at 12:28 PM, Peter Eisentraut wrote:
>> Yes, but if you are asking that question, you probably really want
>> to use
>> TRUNCATE.
>
> The advantage of DROP TABLE being, of course, that DROP TABLE is
> transacti
t; but might wish to do so later.
> Unless I'm deeply misunderstanding something (always a possibility),
> DROP TABLE and TRUNCATE are not symmetrical in this regard. Once a
> transaction has issued a DROP TABLE, all other transactions that
> attempt to modify it are going t
isunderstanding something (always a possibility),
DROP TABLE and TRUNCATE are not symmetrical in this regard. Once a
transaction has issued a DROP TABLE, all other transactions that
attempt to modify it are going to block on the first transaction's
ACCESS EXCLUSIVE lock until it commits or
Christophe writes:
> On Apr 21, 2009, at 12:28 PM, Peter Eisentraut wrote:
>> Yes, but if you are asking that question, you probably really want
>> to use TRUNCATE.
> The advantage of DROP TABLE being, of course, that DROP TABLE is
> transactionally-safe, while TRUNCATE
marek.patr...@gmail.com escribió:
> I was wondering if dropping a table is more efficient in PostgreSQL
> 8.x in comparison to deleting it's content ?
"8.x" is a meaningless version number in Postgres. Major versions (with
new features, etc) are labeled by the first two elements, so 8.0, 8.1,
and
On Apr 21, 2009, at 12:28 PM, Peter Eisentraut wrote:
Yes, but if you are asking that question, you probably really want
to use
TRUNCATE.
The advantage of DROP TABLE being, of course, that DROP TABLE is
transactionally-safe, while TRUNCATE is not.
--
Sent via pgsql-general mailing list
On Tuesday 21 April 2009 20:26:24 marek.patr...@gmail.com wrote:
> I was wondering if dropping a table is more efficient in PostgreSQL
> 8.x in comparison to deleting it's content ?
Yes, but if you are asking that question, you probably really want to use
TRUNCATE.
--
Sent via pgsql-general ma
I was wondering if dropping a table is more efficient in PostgreSQL
8.x in comparison to deleting it's content ?
To put you in the picture - I want to schedule via cron job some daily
data materialization. With the resource I got there are only those two
ways:
1) DROP statement:
a) DROP
"George Pavlov" <[EMAIL PROTECTED]> writes:
> Wouldn't it be cleaner if view operations worked only on views and table
> ones on tables?
It's not worth the backwards-compatibility issues that would ensue.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-ge
This is minor, but just curious about the reasons for the assymetry
between ALTER and DROP with respect to tables vs. views.
* ALTER TABLE seems to work on both tables and views (even though ALTER
VIEW exists, albeit with a limited operation support).
* DROP TABLE works only on tables, not on
Andreas Kretschmer <[EMAIL PROTECTED]> writes:
> Tom Lane <[EMAIL PROTECTED]> schrieb:
>> In 8.2 and up you can use ALTER SEQUENCE ... OWNED BY ... to establish a
>> link that will make a manually created sequence go away when its "owner"
>> column is dropped. In 8.1 that aspect of SERIAL is hidde
Tom Lane <[EMAIL PROTECTED]> schrieb:
> "Scott Marlowe" <[EMAIL PROTECTED]> writes:
> > On 10/10/07, Guilherme <[EMAIL PROTECTED]> wrote:
> >> If I insert a sequence later on table creation with alter table, drop
> >> table cascad
"Scott Marlowe" <[EMAIL PROTECTED]> writes:
> On 10/10/07, Guilherme <[EMAIL PROTECTED]> wrote:
>> If I insert a sequence later on table creation with alter table, drop
>> table cascade simply doesn't drop this sequence even when I specify
>> CASCAD
On 10/10/07, Guilherme <[EMAIL PROTECTED]> wrote:
> Hello folks,
>
> I'm new to postgres and I'm using version 8.1
>
> Here's the problem anyway:
>
> If I insert a sequence later on table creation with alter table, drop
> table cascade simply d
Hello folks,
I'm new to postgres and I'm using version 8.1
Here's the problem anyway:
If I insert a sequence later on table creation with alter table, drop
table cascade simply doesn't drop this sequence even when I specify
CASCADE.
works
create table
> I need to do a "drop table if exists" type thing. I realise I can
Install 8.2 or use this function, posted by David Fetter:
Thanks for your answers... so this really was something that was
missing (I think it a little rich to come out with a "are you using a
version without
Anton Melser <[EMAIL PROTECTED]> schrieb:
> Hi,
> I need to do a "drop table if exists" type thing. I realise I can
Install 8.2 or use this function, posted by David Fetter:
--
-- posted by David Fetter
--
CREATE OR REPLACE FUNCTION drop_table(TEXT)
RETURNS VOID
STRI
I am not sure if I understood the problem correctly!!!
Can you not use the standard command "DROP TABLE IF EXISTS table1" that PG
provides?
http://www.postgresql.org/docs/8.2/interactive/sql-droptable.html
Or is it that you are on a version of PG where "IF EXISTS" syn
Hi,
I need to do a "drop table if exists" type thing. I realise I can
easily look in pg_tables, but for testing (if), don't I need to use a
procedural language? In which case, I will need to install it if it
doesn't exist - but I don't know how to test to see whether
Gabor Siklos wrote:
> Does DROP TABLE free up disk space immediately?
It frees space at COMMIT.
--
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
---(end of broadc
Does DROP TABLE free up disk space immediately?
Thanks,
-Gabor
NOTE: The contents of this e-mail including any attachments may contain confidential or privileged information and are to be read solely by the intended recipient(s). If you are not an intended recipient of this e
table test_table(a text, b integer);
create or replace function del_table(varchar) returns boolean as '
declare
tname alias for $1;
begin
drop table tname;
return true;
end;
' language plpgsql;
select del_table('test_table');
CREATE TABLE
CREATE FUNCTION
psql:16:
-- Forwarded message --
From: Sergey Karin <[EMAIL PROTECTED]>
Date: 11.11.2005 13:59
Subject: [8.1] "drop table" in plpgsql function
To: pgsql-general@postgresql.org
hi all!
I have found a strange error.
panorama2=# select version();
./f_safe_drop_table.sql:
--
create or replace function f_safe_drop_table(varchar) returns bool as'
declare
table_name alias for $1;
tcount int4;
begin
select f_is_table_exist(table_name) into tcount;
if(tcount > 0) then
drop table table_name;
return tru
Brian Hirt <[EMAIL PROTECTED]> writes:
> I'm a bit baffled by this, i can't drop a table and I'm getting the
> most bizarre message that doesn't make sense to me. To make things
> worse, I was able to drop it on my test database which is a dump of
> my production database running the same ve
|
given_to_user_id| integer |
Indexes:
"game_giveaway_pkey" PRIMARY KEY, btree (game_giveaway_id)
basement=# drop TABLE public.game_giveaway ;
ERROR: "game_pkey" is an index
basement=# select ve
am 26.08.2005, um 2:11:30 +0430 mailte Lee Harr folgendes:
> >I have not been able to work out how to do this is Postgres 8
> >(pseudo-code)
> > if exists table foo
> > drop table foo;
> > end
> > create table foo;
> >If I go with
> > drop table
I have not been able to work out how to do this is Postgres 8
(pseudo-code)
if exists table foo
drop table foo;
end
create table foo;
If I go with
drop table foo;
create table foo;
then it barfs on an empty db.
The assumption here is that the SQL is coming in on a script via the
Hi ..
I have not been able to work out how to do this is Postgres 8
(pseudo-code)
if exists table foo
drop table foo;
end
create table foo;
If I go with
drop table foo;
create table foo;
then it barfs on an empty db. I can find the table name in pg_class but
I am not sure of where
On Tue, Dec 07, 2004 at 01:20:07PM +0530, Nageshwar Rao wrote:
> Not able to drop a table,though nobody is accessing the table.I am able to
> insert the records and delete the records.When I give drop table it just
> hangs there .No error message.
Another transaction might be holding
Title: Message
Not able to drop a
table,though nobody is accessing the table.I am able to insert the records and
delete the records.When I give drop table it just hangs there .No error
message.
any specific
reasons
thx
Mark Gibson <[EMAIL PROTECTED]> writes:
> I kept getting the following error:
> ERROR: cache lookup failed for relation 4667548
This implies that something someplace still has a link to the table with
that OID. You could do "\set VERBOSITY verbose" so that the code
location the error occurs at g
Hello,
I've just encountered a problem that I don't know how to deal with.
After having a play with SlonyI, I dropped the entire slony cluster
schema, and then tried to drop a schema which held some slave tables
for the slony cluster.
I kept getting the following error:
ERROR: cache lookup faile
Dragan Matic wrote:
if exists (select * from sysobjects where id =
object_id(N'[dbo].[pp_fisk]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[pp_fisk]
GO
For instance, this is a valid script in Ms SQL, it will drop table
pp_fisk only if it exists,
Mike Mascari <[EMAIL PROTECTED]> writes:
> Is there any possibility that he's got an open transacation sitting out
> there for days holding a lock on that table?
Good thought ... but if that was the issue then the DROP would just be
sleeping waiting for the lock, and Eric did say it was consuming
Tom Lane wrote:
"Eric Freeman" <[EMAIL PROTECTED]> writes:
I started the drop table command yesterday and it's been running for almost
24 hours.
You're stuck in some kind of infinite loop --- there's no way that DROP
should take any noticeable amount
"Eric Freeman" <[EMAIL PROTECTED]> writes:
> I started the drop table command yesterday and it's been running for almost
> 24 hours.
You're stuck in some kind of infinite loop --- there's no way that DROP
should take any noticeable amount of time. I'
ours) so I aborted it.
I tried vacuuming it and that ran for about the same amount of time before I
aborted. I figured the fastest ting would be to drop the table and re-create
it.
I tried running a pg_dump and it ran for about 4 days without putting any of
the data to the output file.
I started the
Hi Tino,
--- Tino Wildenhain <[EMAIL PROTECTED]> wrote:
> David Link wrote:
> > Hi All,
> >
> > Here's a Conditional drop_table func for those interested. There
> was a
> > thread on this a long time back.
> >
> > We do this all the time :
> >
> > DELETE TABLE sales;
> > CREATE TABLE sales
Hi David,
David Link wrote:
Hi All,
Here's a Conditional drop_table func for those interested. There was a
thread on this a long time back.
We do this all the time :
DELETE TABLE sales;
CREATE TABLE sales (...);
Hm. "all the time" enables all the warning lights -
what are you doing to have
int4;
> BEGIN
> SELECT into cnt count(*) from pg_class where relname =
> tablename::name;
> if cnt > 0 then
> execute \'DROP TABLE \' || tablename;
> return tablename || \' DROPPED\';
> end if;
> return tabl
REATE or REPLACE function drop_table (varchar) returns varchar as '
DECLARE
tablename alias for $1;
cntint4;
BEGIN
SELECT into cnt count(*) from pg_class where relname =
tablename::name;
if cnt > 0 then
execute \'DROP TABLE \' || tablename;
r
> We accidentally created a table called user in our public schema. We
> are now trying to remove the table using DROP TABLE user and even DROP
> TABLE public.user. However every time we try we get an error message:
> ERROR: parser: parse error at or near "user" a
"Patrick Hatcher" <[EMAIL PROTECTED]> writes:
> We accidentally created a table called user in our public schema. We are
> now trying to remove the table using DROP TABLE user and even DROP TABLE
> public.user. However every time we try we get an error message: ERROR:
We accidentally created a table called user in our public schema. We are
now trying to remove the table using DROP TABLE user and even DROP TABLE
public.user. However every time we try we get an error message: ERROR:
parser: parse error at or near "user" at character 12
Any suggest
On Wed, 2 May 2001, Andy Koch wrote:
> Is it possible to drop multiple tables with SQL on the system tables:
>
> I tried this:
>
> DROP TABLE from pg_tables where tablename LIKE 'table_num_%';
>
> Which for whatever reason would delete 0 items despite it sh
Is it possible to drop multiple tables with SQL on the system tables:
I tried this:
DROP TABLE from pg_tables where tablename LIKE 'table_num_%';
Which for whatever reason would delete 0 items despite it should have
matched on several.
Of course I'm not even sure pg_tables wou
Hi,
I have a problem when doing drop table within a transaction:
It seems that if you execute a drop table sentence but after the
transaction is aborted because an error occurs, the result is a
corruption of the database: the drop table sentence deletes the physical
files corresponding to the
On Mon, Aug 14, 2000 at 02:56:05PM -0400, Jason Hihn wrote:
> /* please forgive me if you've seen this before. I've tried sending this
> several times, but I have not seen it show up on the list yet. I was
> having some subscribing trouble */
>
> Hello. I was reading through the postgre docs, and
/* please forgive me if you've seen this before. I've tried sending this
several times, but I have not seen it show up on the list yet. I was
having some subscribing trouble */
Hello. I was reading through the postgre docs, and saw that it was
listed to 'fix' the non-autodrop of serials. If that
I am starting to use inheritance between tables and when performing a
pg_dump -c (ie: clean drop schema) the DROP TABLE statements are not
optimally ordered: the "DROP TABLE mother_table" comes before "DROP TABLE
child_table" and a "psql -f dump.sql" fails to clea
Hello!
I have a problem when I try to drop a table
:
redife=> drop table supervisor;ERROR: cannot find
attribute 8 of relation supervisor
this is the error that postgres showme, I had
that problem before with another table and I deleted a file with the same name
of the table in /
81 matches
Mail list logo
