Tag Info

Hot answers tagged

12

Generally, col IS NULL is a possible candidate for a (default) b-tree index search. I quote the manual here: Also, an IS NULL or IS NOT NULL condition on an index column can be used with a B-tree index. To get proof, disable sequential scans in a test session (only!). SET enable_seqscan = OFF; I quote the manual here: enable_seqscan (boolean) ...


7

Usually PostgreSQL 9.3 is generally faster then 8.3 - but hard to say what is wrong. Possible sources: problems with IO, wrong PostgreSQL configuration - max_connections = 1000 is probably terribly wrong value, default work_mem is usually too small, hitting hw limits (9.1 and higher should to better use more CPU), wrong testing ... Other problems can be ...


4

\copy is a psql (commandline) command. It is not a regular SQL command. You will need to use COPY instead (but that requires the file to be present on the database server)


4

This particular example can be simpler. You can TRUNCATE multiple tables at once. Aggregate all tablenames and execute a single statement: CREATE OR REPLACE FUNCTION truncate_tables(_username text) RETURNS void AS $func$ BEGIN EXECUTE ( SELECT 'TRUNCATE TABLE ' || string_agg(quote_ident(t.tablename), ', ') || ' CASCADE;' ...


4

Try dispensing with the explicit cursor: begin; set role dba; create role stack; grant stack to dba; create schema authorization stack; set role stack; -- create table foo(id serial); insert into foo default values; create or replace function truncate_tables(username in varchar) returns void as $$ declare r record; begin for r in (select tablename ...


3

So why does Postgres 9.2 still show a sequential scan? I quote the Postgres Wiki: Is "count(*)" much faster now? A traditional complaint made of PostgreSQL, generally when comparing it unfavourably with MySQL (at least when using the MyIsam storage engine, which doesn't use MVCC) has been "count(*) is slow". Index-only scans can be used to ...


3

You have a couple options. The basic thing is that dead tuples in the middle of the table, as you have guessed are not reclaimed for the filesystem with autovacuum. Rather they are marked as free so that future inserts in the same table can use that space. The space isn't bloated permanently. It is available for re-use within the same table regarding ...


3

Please refer to the postgres manual for COPY. In pgAdmin (or the sql string you pass via a script or other db connection) you would just use COPY with no "\" prefix. so enter something like : COPY tablename.... You need to make sure that you have the relevant privileges to run the command, so in this case you need to be able to log into the database and ...


3

Finally, I have a solution. I'll use the tool PgBouncer with its parameter server_idle_timeout.


2

RETURN QUERY EXECUTE was introduced with Postgres 8.4. Your version is just too old and unsupported by now. Upgrade to a more recent version. Also, dynamic column names in the result are very hard to come by. It's a principle of SQL that it wants to know the return type - including the names - up front. Returning anonymous records without a column ...


1

You can put the database into recovery mode by forcing an unclean shutdown of any of the backend processes, other than the postmaster itself. For example, with kill -9. Yes, PostgreSQL restarts. That is why it going into recovery mode. Recovery mode is a part of starting up again after an unclean shutdown. If the database was idle when it crashed, then ...


1

If you want to force PostgreSQL into recovery, you can create a recovery.conf with no restore_command or streaming replication configuration set. Leave hot_standby set to off in postgresql.conf. Personally I recommend taking a base backup (pg_basebackup -X stream) and using that, rather than doing it to the original DB.


1

First off, you should be aware that PostgreSQL 8.3 will soon be going EOL. You should probably use a more current version. In fact, if you did use a more current version (9.1 or later), you could use Foreign Data Wrappers (FDW). Someone has even posted source code for a TDS FDW, which should work for older SQL Server. If you're somehow stuck with ...



Only top voted, non community-wiki answers of a minimum length are eligible