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


6

select string_agg(c,'') from ( select distinct regexp_split_to_table(lower(name),'') as c from data ) t The inner select generates one row for each character, and the outer then aggregates that to a long string. If you want the characters sorted, you can use an order by for the aggregate string_agg(c,'' order by c)


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


5

Through version 9.3, the indirection array used for sorting had to fit in a single 1GB memory allocation. This created an artificial limit on the number of tuples which could be sorted in memory. Once that limit was reached, it had to switch to a disk sort, even if there was memory left over. This restriction was removed in version 9.4.


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

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

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


4

It shouldn't take long to get up to speed to a reasonable degree if you've used another RDBMS. Read some guidance on PostgreSQL for MySQL users to help you adapt to sequences vs auto_increment, ANSI-standard quoting (though you should be using that in MySQL already), the stricter data type checking, how authentication and roles work, psql's backslash ...


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

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

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


3

if you add a second disk i suggest to put data on to different tablespaces. a tablespace offers additional storage and is VERY easy to use: mkdir /whatever then in SQL: CREATE TABLESPACE myspace LOCATION '/whatever'; ALTER DATABASE x SET TABLESPACE myspace; of course you can also do this for single tables only. and yes, alternatively you can move the ...


3

As developer, not a full-time DBA, I use Postgres for some of my work. But it is not my focus. I found Postgres very confusing and frustrating when getting started. After a long career using 4D, I was an pro in relational database design and normalization, but a newbie with SQL and "black-box" database engines. Below is a list of the resources that helped ...


3

Yes you can. Per documentation: Note that the user performing the insert, update or delete on the view must have the corresponding insert, update or delete privilege on the view. In addition the view's owner must have the relevant privileges on the underlying base relations, but the user performing the update does not need any permissions on the ...


2

Not a direct answer to your question but you should try the first_value window function. It works like this: CREATE TABLE test ( id SERIAL NOT NULL PRIMARY KEY, cat TEXT, value VARCHAR(2) date TIMESTAMP WITH TIME ZONE ); Then, if you want the first item in each cat (category) you will query like that: SELECT cat, ...


2

I would look into partitioning. If partitioned by day, you could just drop the entire partition once it gets too old. You may even no longer have to vacuum. Also, overall performance might increase, since you're not inserting where you're deleting. You would just need to write the code to create new partitions and delete old ones. This is exactly what ...


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

"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

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

Speaking of style, you can improve in several places: In addition to what Craig already wrote. It's inconsistent to have one condition that only involves the doctor table in the JOIN clause, while the other one is in the WHERE clause. Be consistent, both or none. Best to put these in the WHERE clause, while the condition that links both tables goes into ...


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


2

You'll need to either use an ETL Tool eg Microsoft SSIS. There's plenty of open source products available too. Or you can build your own extract utility using a programming language like PHP, .Net etc. The Kimball guys have an article on whether you should build your own or use an off the shelf product. ...


2

Let's assume that the target table of the INSERT ("TableD") has a column with a foreign key to "TableC". An INSERT to this table must acquire a shared lock on "TableC" and will keep that lock until the transaction ends. Say two concurrent transactions, T1 and T2, start with such an INSERT. Each takes a shared lock on "TableC". Later on, in order to do a ...


2

Function for a single table Returns all character-type columns of the given table with a count of empty values ('') and whether they are defined NOT NULL. CREATE OR REPLACE FUNCTION f_tbl_empty_status(_tbl regclass) RETURNS TABLE (tbl text, col text, empty_ct bigint, not_null bool) AS $func$ DECLARE -- basic char types, possibly extend with citext, ...



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