Tag Info

Hot answers tagged

9

"Can support" != "optimal throughput". You can use lots of connections, but it's slower. If you use fewer connections and queue work, you get the same amount of work done in a smaller time. Even more extreme is the discrepancy for another DBaaS provider, who proposes a 2 core server with 500 concurrent connections. How could this possibly work well? ...


8

I would suggest separate Player, Game and Participant tables: Player PlayerID Name Etc. Game GameID -- a unique number which distinguishes any one game from all others. Game_Data -- the logged data of how this one game played out. Participant PlayerID GameID Since each player in a game will see the same game_data it ...


7

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 ...


7

none of the columns are mentioned Yes they are, right here: where pd.active and pd.fk_status = 1 that matches the condition on the index and thus the index can be used to support the counting of the rows. Reading through all the rows in the index should be faster than doing a seq scan on the process_data table. Why it's not using the other index I ...


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/.


5

UPSERT 1 row at a time with function A "table-function" is a function returning a set of rows (acting like a table when called with SELECT * FROM myfunc()). What you have is not a table-function. Since nothing is returned you can use a simple call: SELECT merge_vehicles(vid, cid, vname, reg_no, name, name_1st) FROM ( VALUES (2335, 55, '246BDH', ...


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

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

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 ...


4

You wrote "case closed", but I'll reopen. There is just too much gone wrong ... Database design and test setting CREATE TABLE patient ( patient_id int PRIMARY KEY , site_held_at int NOT NULL ); CREATE TABLE messageq ( messageq_id varchar PRIMARY KEY -- varchar ?! , patient_id int NOT NULL REFERENCES patient , message_body varchar NOT NULL ...


4

First of all, there is no "trigger body" (unlike Oracle). In Postgres you have a trigger function (also called procedure) with a function body and 0-n triggers (without body) calling this function. The special variable NEW in plpgsql trigger functions is neither a map nor an array; it's a row: NEW Data type RECORD; variable holding the new database ...


3

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 "}


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

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.


3

In this special case the column actually indexed is irrelevant for the query at hand. You can pick any column. I would pick something else than uploaded_at, which is useless. Some column that may be useful for other queries and is not bigger than 8 bytes ideally. CREATE INDEX foo ON table bar (some_col) WHERE uploaded_at IS NULL; If you have no use case ...


3

No, and no, though it does have some heuristics (like join_collapse_limit) to limit how long it tries for, and it also has things like GEQO to switch to different optimisation strategies for big joins under some circumstances. There's no optimisation timeout as such. (It'd help if you'd explain the kinds of queries you're running and why they're taking so ...


3

A role is an entity that can function as a user and/or as a group. A role WITH LOGIN can be used as a user, i.e. you can log in with it. Any role can function as a group, including roles that you can also log in as. So "user" and "group" are essentially terms that indicate the intended usage of a role, there's no real distinction between them. Even in the ...


3

Short answer: NO!. Longer answer: If you use synchronous NFS and you have robust STONITH / fencing, then with great caution you could run a shared-storage failover cluster over NFS without horribly corrupting your data. However, it won't do you any good for failure modes where the data is affected by the problem. Shared-access SAS/SCSI, DRBD, shared-access ...


3

A foreign key can not be "conditional". The only "exception" to that rule are null values which can't reference another table by definition. If I understand your question correctly, you are trying to implement a constraint that says "if refunded is true then refund_id must reference an existing transaction". I don't think you need the refunded column at ...


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

"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 ...


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 ...


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

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

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

According to the true nature of things (as I understand it), I suggest two tables: CREATE TABLE product ( product_id serial PRIMARY KEY , product text NOT NULL -- more attributes of the product ); CREATE TABLE offer ( offer_id serial PRIMARY KEY , product_id int NOT NULL REFERENCES product , price int -- prices in cent , ...


2

The syntax is almost correct. You need the keyword values and some commas that are missing: with x (x, y, z) as (values (2, 5, 6), (6, 3, 2) ) select * from x;


2

None of those parentheses are necessary. Efficiency is identical for an inner join (so long as you're under join_collapse_limit). For join-lists bigger than PostgreSQL's join-reordering limit the extra terms in the ON predicate may be faster though. Focus on style. Is it logically a part of the condition that joins one table to another? Put it in the ON ...



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