[pg93@db1 bin]$ pg_ctl restart -m fast -D $PGDATA waiting for server to shut down.... done server stopped server starting [pg93@db1 bin]$ LOG: 00000: loaded library "pgtrashcan" LOCATION: load_libraries, miscinit.c:1296 LOG: 00000: redirecting log output to logging collector process HINT: Future log output will appear in directory "pg_log". LOCATION: SysLogger_Start, syslogger.c:649
francs=> create table test_trash(id serial primary key, name character varying(64),create_time timestamp(6) without time zone); CREATE TABLE francs=> insert into test_trash(name,create_time) select generate_series(1,100000)* random() || 'a',clock_timestamp(); INSERT 0100000
francs=> create index idx_test_trash_ctime on test_trash using btree (create_time); CREATE INDEX
francs=> /d test_trash Table"francs.test_trash" Column | Type | Modifiers -------------+--------------------------------+--------------------------------------------------------- id | integer | not null default nextval('test_trash_id_seq'::regclass) name | character varying(64) | create_time | timestamp(6) without time zone | Indexes: "test_trash_pkey" PRIMARY KEY, btree (id) "idx_test_trash_ctime" btree (create_time)
francs=> select * from test_trash limit 3; id | name | create_time ----+--------------------+---------------------------- 1 | 0.939794037491083a | 2014-04-0211:22:09.238519 2 | 1.51998516358435a | 2014-04-0211:22:09.239463 3 | 1.02361420495436a | 2014-04-0211:22:09.239518 (3 rows)
以普通用户删除表
1 2 3 4
francs=> /c francs francs You are now connected to database "francs" as user "francs". francs=> drop table test_trash ; ERROR: permission denied for schema Trash
francs=# drop table francs.test_trash ; DROP TABLE francs=# /dn List of schemas Name | Owner --------+---------- Trash | postgres francs | francs public | postgres (3 rows)
francs=# /dt "Trash".* List of relations Schema | Name | Type | Owner --------+------------+-------+-------- Trash | test_trash | table | francs (1 row)
francs=# /ds "Trash".* List of relations Schema | Name | Type | Owner --------+-------------------+----------+-------- Trash | test_trash_id_seq | sequence | francs (1 row)
francs=# alter table "Trash".test_trash set schema francs; ALTER TABLE
francs=# /dt+ francs.test_trash List of relations Schema | Name | Type | Owner | Size | Description --------+------------+-------+--------+---------+------------- francs | test_trash | table | francs | 5912 kB | (1 row)
备注：恢复删除的表只需要更改表的 schema 即可。
函数删除测试
创建函数
1 2 3 4 5 6
francs=> CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$ francs$> BEGIN francs$> RETURN subtotal * 0.06; francs$> END; francs$> $$ LANGUAGE plpgsql; CREATE FUNCTION
删除函数
1 2 3 4 5 6 7 8
francs=> drop function sales_tax(subtotal real) ; DROP FUNCTION
francs=> /df "Trash".* List of functions Schema | Name | Result data type | Argument data types | Type --------+------+------------------+---------------------+------ (0 rows)
备注: 函数删除后，并不会移到 “Trash” 模式下。
永久删除表
如果想永久删除此表，可以删除 “Trash” 模式下的这张表或者删除整个 “Trash” 模式。
1 2 3 4 5
francs=> /c francs postgres You are now connected to database "francs" as user "postgres". francs=# drop schema "Trash" cascade; NOTICE: drop cascades to table "Trash".test_trash DROP SCHEMA
