Posts

Showing posts with the label COPY

New in PostgreSQL 9.3: Server Side languages

In series of blogging about new features coming in PostgreSQL 9.3, I thought to blog about the server side language improvements in PostgreSQL. Lets see whats coming in server side language. As PostgreSQL user, you know, PostgreSQL supports multiple server side language. In 9.3, there are some interesting features are coming. Lets look at the new improvements about to come. 1. SPI access to number of rows processed by COPY command. This is more like new feature which is introduced in 9.3. Before 9.3, this feature was missing. i.e if user uses COPY command inside the plperl/plpython functions, then there was no way inside the procedure to trace the number of rows processed by COPY. However in 9.3, this limitation is no more exists with procedural language. There are many languages supported in PostgreSQL, However I chose to test this with mostly used language plperl and plpython. Below are some snapshot pre-9.3 and in 9.3. Lets check with plperl. Following is a plperl function w

Utility Operations improvement in PostgreSQL 9.1

1. Transaction-level advisory locks: PostgreSQL provides a means for creating locks that have application-defined meanings. These are called advisory locks, because the system does not enforce their use — it is up to the application to use them correctly.  Till 9.0, postgreSQL had only Session level locks. Now, in PostgreSQL 9.1, we have transaction level advisory lock. Some examples are given below: BEGIN; -- grabbing txn locks multiple times SELECT pg_advisory_xact_lock(1), pg_advisory_xact_lock(1), pg_advisory_xact_lock_shared(2), pg_advisory_xact_lock_shared(2), pg_advisory_xact_lock(1, 1), pg_advisory_xact_lock(1, 1), pg_advisory_xact_lock_shared(2, 2), pg_advisory_xact_lock_shared(2, 2); SELECT locktype, classid, objid, objsubid, mode, granted FROM pg_locks WHERE locktype = 'advisory' ORDER BY classid, objid, objsubid; COMMIT; Advantage of Advisory lock are given below: 1. Advisory locks are faster 2. It avoid

COPY data at Client Side in PG.

Lot of People think that COPY is only a Server Based Command. However its not completely true. User can copy the data of a table at client side using COPY Command. The Difference is only in syntax usage. User has to use backSlash before COPY Command as given below: testdb=# select * from test; id ---- 1 2 3 (3 rows) testdb=# \copy test to '/tmp/test.copy' testdb=# \q We can see output of a file at Client side as given below: cat /tmp/test.copy 1 2 3 Similarly User can also copy the data in a file to Server using \COPY as given below: \COPY test from '/tmp/test.copy' iclive1460=# \COPY test from '/tmp/test.copy' iclive1460=# select * from test; id ---- 1 2 3 1 2 3 (6 rows) Note:: One should remember while using \COPY Command, one should not terminate command with semicolon ';'