Tag Info

Hot answers tagged

11

I believe this is by design, according to the description of the read-committed isolation level for PostgreSQL 9.2: UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave the same as SELECT in terms of searching for target rows: they will only find target rows that were committed as of the command start time1. However, such a target row ...


9

Mat and Erwin are both right, and I'm only adding another answer to further expand on what they said in a way which won't fit in a comment. Since their answers don't seem to satisfy everyone, and there was a suggestion that PostgreSQL developers should be consulted, and I am one, I will elaborate. The important point here is that under the SQL standard, ...


6

I completely agree with @Mat's excellent answer. I only write another answer, because it wouldn't fit into a comment. In reply to your comment: The DELETE in S2 is already hooked on a particular row version. Since this is killed by S1 in the meantime, S2 considers itself successful. Though not obvious from a quick glance, the series of events virtually is ...


6

BLOB is correct, as that is a binary string. TEXT is a character string, but protobuf is not character data; so use some kind of BLOB As for TINY/MEDIUM/LONG; how big is your data? TINYBLOB : max 255 bytes BLOB : max 65,535 bytes MEDIUMBLOB : max 16,777,215 bytes LONGBLOB : max 4,294,967,295 bytes In some small minority of cases TINYBLOB may suffice, ...


5

The table should be create table cali ( id serial primary key, alk_from bigint, alk_to bigint, and_from int, and_to int ); And do \COPY cali (alk_from, alk_to, and_from, and_to) FROM '/home/.../data/output/id_cali.csv' (FORMAT CSV); So that it knows to not insert into the id serial column. The serial type is not a true type. From ...


4

What you see is completely normal and expected. Note: Because smallserial, serial and bigserial are implemented using sequences, there may be "holes" or gaps in the sequence of values which appears in the column, even if no rows are ever deleted. A value allocated from the sequence is still "used up" even if a row containing that value is never ...


4

Can READ COMMITTED be set to indefinitely retry with the same atomicity as SERIAZLIZABLE? No. READ COMMITTED doesn't retry. Neither does SERIALIZABLE. The application is expected to retry transactions that suffer deadlocks, serialization failures, etc. That description in docs is very misleading, I'll raise it on the docs list. PostgreSQL doesn't ...


3

I was assuming that these new records will not be visible to the current transaction, but proved wrong on testing. That's not the case in the default READ COMMITTED isolation. Changes from committed transactions become visible at the start of the next statement in a transaction. Each statement still has a snapshot, so you can't have rows appear within a ...


3

This is a bit of a FAQ. You'd find more information if you searched for ON DUPLICATE KEY UPDATE (the MySQL syntax), MERGE (the SQL-standard syntax), or UPSERT. It's surprisingly hard. The best article I've seen on it yet is Depesz's "why is upsert so complicated". There's also the SO question Insert, on duplicate update (postgresql) which has suggestions ...


3

No SQL Server will not be able to write directly to a serial port or TCP socket. You'll need to either write an application that pulls the data from the SQL Server, or a SQL CLR procedure that calls out to the TCP IP address and socket. I'd recommend using an app to query the SQL Server database then write that data to the serial port or TCP socket.


2

You have an unresolved naming conflict. You must be using an old version of Postgres without declaring it. Or you are operating with non-default configuration setting. Here you declare a variable named measurement_id: DECLARE measurement_id INTEGER; It's a folly to use ambiguous variable names to begin with. If you do it anyway, you ...



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