Tag Info

Hot answers tagged

7

A LANGUAGE sql function must be a complete SQL statement (or more than one). Yours is just an expression. Just prepend SELECT, like you would when running it stand-alone. $BODY$ SELECT (SELECT ... The reason it complains at the + is that, surprisingly, it's legal to parenthesise a top-level query. This, for example, is a valid query: regress=> ...


5

except can be re-written as not exists query: select t1.id, t1.amount, t1.email, t1.date from t1 where not exists (select 1 from t2 where t2.amount = t1.amount and t2.email = t1.email and t2.date = t1.date); But your statement seems to indicate you do not actually want except but ...


3

The question is: is this sufficient or there should be more grants (database connect, usage maybe)? Security may be hardened from the default mainly on these points: The pg_hba.conf file. It filters connections before any database privilege is considered. The default is relatively open for local connections, but it might be restricted to an explicit ...


3

The pg_ctl status command tests that the postmaster process (corresponding to $PGDATA) does exist from the point of view of the operating system. In the positive case, it reports that it's running (note that the word available is not used). Essentially that's implemented by reading postmaster.pid and checking the result of kill(that_pid, 0). It does not ...


3

You half miss the connection between the two tables. The ...relid columns must match, too: SELECT attname, c.* FROM pg_attribute a JOIN pg_constraint c ON attrelid = conrelid -- this was missing AND attnum = ANY (conkey) WHERE attrelid = 'test_table'::regclass::oid;


2

Let's simply try it! I've created the table from your question, then did the following: INSERT INTO mytable SELECT json_object(ARRAY['myfield', i::text || 'bla'])::jsonb FROM generate_series (1, 100000) t(i); \di+ trgm_index_1 Schema │ Name │ Type │ Owner │ Table │ Size │ Description ...


2

Yes. Everything you did inside the same transaction is visible to later commands inside the same transaction. Just not to other transactions until committed. This is true for all isolation levels except Read uncommitted where "dirty reads" are possible (but that does not affect your question per se). It's implemented with the MVCC model (Multiversion ...


2

If you have WAL archiving enabled on the master (archive_command is set and archive_mode is on), set a restore_command in your replica's recovery.conf to allow it to fetch WAL from the WAL archive. If there's no WAL archive, then there's no record of needed deltas between the master and the replica anymore. So you must resync them. Typically this is done ...


2

It sounds like you might be looking for pg_comparator. I haven't used it myself, but I've got some database check/compare work to do soon, and it's one of the options I need to look into as a starting point.


1

In my opinion it is not related to mklink or Windows issues. Per pg_basebackup docs, the main data directory will be placed in the target directory, but all other tablespaces will be placed in the same absolute path as they have on the server. -F format --format=format Selects the format for the output. format can be one of the following: p ...


1

Here you are a few steps: Install postgreSQL 9.x from packages; Configure ubuntugis repository from here; Add the following lines to /etc/apt/source.list with your ubuntu version: deb http://ppa.launchpad.net/ubuntugis/ubuntugis-unstable/ubuntu YOUR_UBUNTU_VERSION_HERE main deb-src http://ppa.launchpad.net/ubuntugis/ubuntugis-unstable/ubuntu ...


1

This part generating recovery.conf is suspicious: echo "restore_command = 'cp -f $PGDATA/pg_xlog/%f $PGDATA/pg_xlogRestored'" >>$PGDATA/recovery.conf It seems that the source and destination of this restore_command are reversed. Compare yours to the example from the doc in Continuous archiving: restore_command = 'cp /mnt/server/archivedir/%f %p' ...


1

The message: requested WAL segment 00000001000018F70000008A has already been removed Means that the master hasn't kept enough history to bring the standby back up to date. Since you are using version 9.1, you can use pg_basebackup to create a new slave. We use a command like: pg_basebackup -h masterhost -U postgres -D path --progress --verbose -c fast ...


1

UPDATE appointments SET recurring = 0 WHERE id = 433 AND time = (select max(time) from appointments where id = 433); ... is the answer. You just needed brackets.


1

Let's try : CREATE OR REPLACE FUNCTION public.sp_get_user() RETURNS json LANGUAGE plpgsql AS $function$BEGIN INSERT INTO users (name, password) VALUES ('deadeye', 'test'); RETURN row_to_json(row) FROM (SELECT name, password FROM users WHERE name = 'deadeye') row; END;$function$ Now, let's test : SELECT sp_get_user(); ...


1

Running a diff on a pg_dump from both databases is a very good way to find the differences. The output pg_dump produces is valid SQL and easy to read.


1

All things considered, I would go with option 2. Dates will be evenly selected on, but I'm going to guess that for a given query only one or two date partitions will be involved. It's a shame you can't cluster on geolocation and partition on date, which would be ideal. Elevation tends to correlate with geolocation anyway, if the bounding boxes are ...


1

The problem had to do with a kernel feature called Transparent Huge Pages (THP.) You can diagnose this with perf top: 59.73% postmaster [kernel.kallsyms] [k] compaction_alloc 1.31% postmaster [kernel.kallsyms] [k] _spin_lock 0.94% postmaster [kernel.kallsyms] [k] __reset_isolation_suitable 0.78% postmaster ...


1

This query should get only lines from t1 where amount, email and date match exactly with one line of t2. SELECT t1.ID, t1.amount, t1.email, t1.date FROM t1 INNER JOIN t2 ON t1.amount = t2.amount AND t1.email = t2.email AND t1.date = t2.date;


1

If you already have the PK value of a certain row in A, a simple SELECT is the fastest possible way: SELECT * FROM b WHERE a_id = ? If you need to retrieve a_id first, a join is your best options. Make that a LEFT [OUTER] JOIN only if there can be cases where there are no matching rows in B and you still want a_id in that case (or whatever, you didn't ...



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