Hans-Jürgen Schönig
  • Member for 7 years, 2 months
  • Last seen more than a month ago
Transactions within a Transaction
Accepted answer
17 votes

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

View answer
Why does a SELECT statement dirty cache buffers in Postgres?
Accepted answer
11 votes

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

View answer
How to manipulate join order of PostgreSQL optimizer?
Accepted answer
5 votes

You can do that but it needs some trickery. Imagine you got this: select ... from a, b, c, d, e ... ...this type of query is always reordered. But if you do: select ... from a JOIN b ... JOIN c ...

View answer
Prevent Primary Key increment when an INSERT statement fails in PostgreSQL
Accepted answer
5 votes

this is exactly what you are facing: test=# CREATE SEQUENCE seq_a; CREATE SEQUENCE test=# SELECT nextval('seq_a'); nextval --------- 1 (1 row) test=# BEGIN; BEGIN test=# SELECT nextval('...

View answer
Copying database to different hard disk
4 votes

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

View answer
SELECT all substrings (n-grams) of length n?
4 votes

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

View answer
PostgreSQL procedural languages overhead (plpython / plsql / pllua...)
4 votes

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

View answer
Postgresql - Streaming replication - STONITH
3 votes

well, you cannot switch back easily. what you can do is to use the old backup method: SELECT pg_start_backup('whatever'); rsync the data dir SELECT pg_stop_backup(); this is the best way as not all ...

View answer
Memory usage of PostgreSQL
3 votes

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

View answer
Partial db dump/restore
3 votes

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

View answer
pg_stat_activity - is it possible to decompose a stored procedure further?
2 votes

There is a system view called pg_stat_user_functions. If track_functions has been set to all, you can simply do ... SELECT * FROM pg_stat_user_functions. self_time is the time needed by the function ...

View answer
Which tool to compare an Oracle database with a Postgresql database?
2 votes

as far as i know there is no such tool around. however, you got a couple of choices: a.) use oracle_fdw and just join the stuff or b.) export data to text files and use a simple UNIX diff. it works ...

View answer
Does PGStrom calculate relational comparisons?
Accepted answer
2 votes

no, pgstrom cannot do that. it only able to scan relations - it is not able to join. what you an do is pack an operator into a GPU routine but = is not a good candidate for that.

View answer
Postgresql function to create table
2 votes

yes, this is possible. however, you have to be a little careful. DDLs in a stored procedure USUALLY work. in some nasty corner cases you might end up with "cache lookup" errors. The reason is that a ...

View answer
postgresql - a local replication entry doesn't work
2 votes

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

View answer
Can i have one foreign key to both parent and it's childs in postgresql?
2 votes

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

View answer
PL/pgSQL - Indexes vs Collation vs Pattern Ops
2 votes

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

View answer
Postgres inherit indexes for partitioned tables
2 votes

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

View answer
MongoDB Benchmark with write-intensive on geo-indexed DB
2 votes

i have used PostGIS for over a decade now and I can tell you for sure that there is no match for it in the NoSQL world. how many rows do you have? how large is the thing? Mongo is definitely not ...

View answer
Optimizing for data that does not fit in memory
2 votes

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

View answer
Speed of WAL send/receive when replicating
1 votes

You can use pg_stat_replication along with pg_xlog_location_diff. Checking the differences can tell you how many bytes of xlog have flown from the master to the slave.

View answer
How to create a citation/quote from a Postgres text field?
Accepted answer
1 votes

what you need is the ts_headline function. it does exactly what you need it seems. here is the documentation: http://www.postgresql.org/docs/9.4/static/textsearch-controls.html

View answer
When to break large delete queries up?
Accepted answer
1 votes

from experience i can say that one large delete is usually better. however, there can be some corner cases with IN, which might invalidate my statement but basically this is true. Make sure your got ...

View answer
How can I simplify this UNION?
1 votes

in ANSI SQL the DISTINCT is totally pointless here. UNION automatically filters duplicates: test=# SELECT 1 UNION SELECT 1; ?column? ---------- 1 (1 row) There is a sharp distinction ...

View answer
pgbench-tools failed authentication
1 votes

I strongly believe that only the "local" record is set to trust. try something like that: local all all trust host all all ...

View answer
How to restore postgreSQL schema dump to schema with different name
1 votes

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

View answer
Size of Postgres database growing
1 votes

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

View answer
Postgresql single base on multiple HDD partitions
1 votes

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

View answer
1 million strings and exact match search
1 votes

PostgreSQL is perfectly fine for this. You got a couple of options to make this work. First of all: PostgreSQL has a special index type for that called GIN (http://www.cybertec.at/gin-just-an-index-...

View answer
Do Postgres GIN indexes need to be rebuilt periodically?
0 votes

Well, it depends a bit on the use case. Reusing item pointers in GIN is definitely a lot harder than it is in a btree because the structure is totally different on the item pointer level. With the new ...

View answer