As far as I understand, the fact that our query is waiting for a lock means it has always been waiting for a lock, and it has never changed anything.
Right -- if you see that pg_stat_activity.waiting is "true" for an ALTER TABLE, that almost certainly means that it's patiently waiting for the ACCESS EXCLUSIVE lock on its target table, and its real work (rewriting the table if necessary, changing catalogs, rebuilding indexes, etc.) hasn't started yet.
Is it safe for us to outright cancel our ALTER TABLE query? Or is it possible that the query has already modified something and cancelling it would leave our database in a halfway state of some kind?
Canceling queries (or, equivalently, rolling back a transaction) in PostgreSQL doesn't have any database corruption hazards which you might have been spooked by in certain other databases (e.g. the terrifying warning at the bottom of this page). That's why non-superusers are, in recent versions, free to use pg_cancel_backend()
and pg_terminate_backend()
to kill their own queries running in other backends -- they are safe to use without fretting about database corruption. After all, PostgreSQL has to be prepared to deal with any process getting killed off e.g. SIGKILL from the OOM killer, server shutdown, etc. That's what the WAL log is for.
You may have also seen that in PostgreSQL, it's possible to perform most DDL commands nested inside a (multi-statement) transaction, e.g.
BEGIN;
ALTER TABLE foo ...;
ALTER TABLE bar ...;
-- more stuff
COMMIT; -- or ROLLBACK; if you've changed your mind
(awesome for making sure that schema migrations go in either all-together or not at all.) You said, though:
We did not wrap the ALTER TABLE
in a transaction.
That's fine for a single command -- from the docs,
PostgreSQL actually treats every SQL statement as being executed within a transaction. If you do not issue a BEGIN command, then each individual statement has an implicit BEGIN and (if successful) COMMIT wrapped around it. A group of statements surrounded by BEGIN and COMMIT is sometimes called a transaction block.
So canceling that ALTER TABLE
, either through pg_cancel_backend()
or a Ctrl-C issued from the controlling psql prompt, will have a similar effect as if you had done
BEGIN;
ALTER TABLE ... ;
ROLLBACK;
(though as you hopefully got to see, canceling that expensive ALTER TABLE
can save the database from a lot of unnecessary grinding if you're just going to ROLLBACK
anyway.)
No sane DBA will ever allow such a procedure. This is a SQL injection privilege escalation vector. I can pass in the tablename 'x; exec sp_myfoo;'
and voila.
There are basic issue:
- table names are
NVARCHAR
, not VARCHAR
- table names are length 128, not 60
sysname
is a handy type to represent object names, is an alias for NVARCHAR(128)
- tables are qualified by schema and name, not by name only
- procedures that build dynamic SQL must properly quote the names, using
QUOTENAME
Lets try again:
CREATEPROCEDURE [dbo].[spTruncate]
@schemaName sysname,
@tableName sysname
WITH EXECUTE AS CALLER
AS
SET NOCOUNT OFF;
DECLARE @QUERY NVARCHAR(max);
SET @QUERY = N'TRUNCATE TABLE ' + QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName );
EXECUTE sp_executesql @QUERY;
No need to use execute as OWNER. As CALLER is fine, as long as the procedure is properly signed. Ask you DBA to sign the procedure following the standard procedure. See Module Signing and Signing Stored Procedures in SQL Server. Your DBA should know how to do this.
Best Answer
I performed the
DROP
command and it was instantaneous.I did not attempt to wrap the
DROP
andALTER PROC
in a transaction however.