Tag Info

Hot answers tagged

5

This has a simple reason. In PostgreSQL a row has to go through a visibility check. On the first read, the system checks if a row can be seen by everybody. If it is, it will be "frozen". This is where the writes come from. Similarly, VACUUM also sets bits. There is a detailed explanation: http://www.cybertec.at/speeding-up-things-with-hint-bits/.


4

what you would need is a so called "autonomous transaction" (a feature provided by oracle). at this point this is not possible in PostgreSQL yet. however, you can use savepoints: BEGIN; INSERT ... SAVEPOINT a; some error; ROLLBACK TO SAVEPOINT a; COMMIT; it is not entirely an autonomous transaction - but, it allows you got get "every transaction" right. ...


4

You could try it yourself: WARNING: there is already a transaction in progress It starts no new (sub)transaction as nested transactions are not implemented in PostgreSQL. (You may do some magic in a pl/pgsql function, for example, that mimics that behaviour, though.)


4

SELECT set_config('search_path', 'fred,'||current_setting('search_path'), false); The false says it's not a transaction-LOCAL setting. For the bonus question, you can store the value in a custom setting: SELECT set_config('tmp.search_path', current_setting('search_path'), false); From version 9.2 on, you don't even have to define this setting in ...


4

I think this is because you only ever return the first row from the query's result. The select ... into ... will only retrieve one row and the query select * from result returns only that single record: You also don't need a PL/pgSQL function, a plain SQL function will work just fine: CREATE OR REPLACE FUNCTION getMessageFromSites(ids TEXT) RETURNS ...


3

On PostgreSQL 9.1, it is possible to get to this state without directly manipulating the catalogs. I started from having plpgsql as an extension, and did the following: # DROP EXTENSION plpgsql CASCADE; -- do this only if you are prepared to lose your plpgsql functions, too # \dx List of installed extensions Name ...


3

This does the trick: SELECT SUBSTRING('example' FROM n FOR 3) FROM GENERATE_SERIES(1, LENGTH( 'example' )-2, 1) n; exa xam amp mpl ple Here it is in a function: CREATE OR REPLACE FUNCTION ngrams(varchar, integer) RETURNS SETOF TEXT AS $$ SELECT SUBSTRING($1 FROM n FOR $2)::TEXT FROM GENERATE_SERIES(1, LENGTH($1)-($2-1), 1) n; $$ LANGUAGE SQL; Use it ...


3

How about trying to solve this problem with Point-In-Time-Recovery? A dump has the problem that you got an old set of data anyway. I highly suggest to use a backup method giving you ways more recent version of the data. You can also write yourself a simple change-log trigger storing all the information which has changed in a simple table. Here is how it ...


3

this is pretty hard to tell. it really depends on what you are doing. for example: PL/pgSQL is wonderful if you got large SQL statements in it - it really goes crazy if you got all kinds of branching, substring management and all that. you really got to test from case to case.


2

the easiest thing is: dump the stuff. open the dump file in vi. fix, what has to be fixed and replay the stuff.


2

A UNIQUE constraint is not a trigger. It is implemented by way of a unique index. So it cannot be turned off with --disable-triggers. "Other objects" that depend on a the unique constraint are typically foreign key constraints. Those cannot exist without a unique (or primary key) constraint on the referenced column(s). To enable the restore, you could ...


2

psql generally doesn't need much memory when playing large SQL files, since it doesn't buffer the whole file, only one query at a time, or it uses a COPY stream. The main situation when it may run out of memory is not when importing, but when SELECT'ing a large resultset, especially on 32 bits systems. This situation is generally solved by setting ...


2

shared_buffers are statically taken at startup and are never resized. effective_cache_size is just a hint to the optimizer. it is never allocated. it merely gives a hint of what is going on. so shared_buffers is what you see as taken.


2

this is not possible out of the box. inheritance in PostgreSQL is based on individual tables, which are only logically connected through pg_inherit. Each table is still somewhat independent. Therefore you cannot create a key on the entire structure. It is only possible to reference individual tables. What you can do, however, is to write a trigger ensuring ...


2

There is a module in PostgreSQL which supports trigrams (not n-grams). it is called pg_trgm. Do the following: CREATE EXTENSION pg_trgm; then ... SELECT show_trgm('hello'); The result will be {" h"," he",ell,hel,llo,"lo "}


2

Assuming someval is actually an auto-generated column like a serial. In Postgres this data-modifying CTE (writeable CTE) would do the job: WITH sel AS ( SELECT r.someval, i.someval_str, i.machine FROM (SELECT ?, ?) AS i(someval_str, machine) LEFT JOIN new_data_someval_ref r USING (someval_str) ) , ins AS ( INSERT INTO ...


2

It looks like whatever client you are using is confused about the text encoding; it's sending utf-8 bytes as if they were latin-1, probably. Check: SHOW client_encoding; SHOW server_encoding; locale command in your terminal, if using psql Your update is substituting the octal bytes \303\244 which are the utf-8 encoding for "รค" (U+00E4). You're not ...


1

"It depends". If the client vanishes due to network connection loss the query will generally run until it's retrieved enough rows to fill its network send buffer, then stop and get stuck until the TCP connection drops, at which point it'll abort. If it completes before it fills the TCP send buffer it'll complete successfully, so if it's autocommit the query ...


1

The problem is: "localhost" can be UNIX sockets, IPv4 or IPv6. The first rule you passed is for UNIX sockets. Try to add a rule for IPv4 and one for IPv6. It should work then. Btw, a SIGHUP is enough in case you change pg_hba.conf.


1

What you also could do is to set up a table where only the IDs are stored: CREATE TABLE common (id serial PRIMARY KEY); Then change both your parent and child so that their primary keys are also foreign keys referring to this table: CREATE TABLE parent ( id integer PRIMARY KEY REFERENCES common (id), ... ); Similarly, any table referring to ...


1

UDFs in interpreted languages are pretty much always slower than UDFs written in C or built-in functions, all other things being the same. Each language binding has different code to connect PostgreSQL to the language, with different degrees of optimisation, different ways of passing some data types, etc. So variation certainly exists. It shouldn't be huge. ...


1

If I understand you correctly you want a comma separated list of triggering_id's select source_id, string_agg(triggering_id::text,',') as id_list from notifications group by source_id;


1

I would go for: CREATE TABLESPACE any_name LOCATION '/any_empty_dir'; ALTER TABLE any_tables SET TABLESPACE any_name; you can do this for a couple of tables to move them to a different filesystem. it should be pretty easy to do. you can also put entire databases on to different filesystems.


1

consider using the citext data type available in contrib. you don't have to do this upper / lower nightmare anymore. for the regular expression type you can use gist along with gist_trgm_ops. it will boost regular expressions nicely.


1

INHERITS itself will never inherit indexes. it will inherit columns only.


1

6 GB a month is only 72 GB a year. so to get to 1 TB of data it is around 15 years. clearl, the amount of data should not be your concern unless you are really doing something fancy. what kind of queries do you run to require such a small database?


1

it is important to know, which queries you are running. otherwise it is close to impossible to give you any real advise. however, there are two things you should know: UPDATE benefits nicely from a thing called FILLFACTOR. it can speed up things a lot. the order of columns does have an impact on the size of the table ...


1

Your question is not very clear. You say you are trying to create a database of cities and zip codes, but then you say you are using Geonames, which is a database of cities and zip codes. Why are you trying to re-create it? Places are not hierarchical. You need to think about the institution responsible for the place's boundaries. State boundaries are ...


1

extract() the epoch from the time component after casting to time (effectively removing the "day" component): SELECT extract(epoch FROM ts::time) AS sec_of_day You get the "number of seconds", including fractional seconds if there are any. Very short and fast. Test (with timestamps in unambiguous ISO format): SELECT extract(epoch FROM ts::time) AS ...


1

You could do this using one of the following queries: select date_part('second', timestamp '2011-02-01 20:23:43'); select to_char(timestamp '2011-02-01 20:23:43', 'SS'); select extract(second from timestamp '2011-02-01 20:23:43'); Have fun.



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