SOC II Type 2 report.
HI , To whom it may concern, Prokopto is completing our annual vendor review process. Please share your most recent SOC II Type 2 report. Thank you! Regards, Raj Kiran.S
Reproducing incorrect order with order by in a subquery
Hello, For example I have a query: SELECT main.*, count(*) OVER () FROM (SELECT DISTINCT ... ORDER BY X) main; So the `ORDER BY` clause ended up in a subquery. Most of the time ordering works until it doesn't. Can you help me create a set of test tables with some data to reproduce this problem more repeatedly? I just want to write a regression test to make sure it doesn't happen again. -- Best regards, Ruslan.
[Beginner Question] Will the backup wal file take too much storage space?
Hi community, When I learn the wal log, a question confuse me. As you can see, base on the wal, we can restore the database to any check point, but sometimes, If we insert a large column to the postgres (Up to 1 GB), the postgres will also create a 1 GB wal to store it. In this situation, will the backup wal file take too much storage space? Thanks in advance! Yours, Wen Yi
"paths" between two ROLEs
Hi. We emulated a legacy security model (enforced in C/C++ code) into "layers" of PostgreSQL ROLEs and GRANTs, thus enforced database-side. To troubleshoot and validate that emulation, I'd like to introspect ROLE membership to: 1) Output the ROLE "path(s)" between any two ROLEs. Typically between the LOGIN USER and the ROLE that control access to a particular SCHEMA. In our model, there can be several ways the two end-roles are connected, involving a variable number of roles. So it has to be a recursive query. 2) target-end ROLEs (controlling access to SCHEMAs, again) follow a naming convention, so they can be identified using a LIKE pattern. Output all target ROLEs (aggregating each "paths" to the source-ROLE in an text[]) a given LOGIN USER has access to. I'd appreciate either example SQL for the above; or hints to achieve the above. My CTE "foo" is not great, thus reaching out to the community to avoid wasting too much time on this on my own. Thanks, --DD
pg_upgrade v15 not generating analyze_new_cluster.sh
Hi, all. When pg_upgrading from PG 11 to 13, a file analyze_new_cluster.sh was generated in the current directory which ran vacuumdb --all --analyze-in-stages When upgrading from 13 to 15.3 no such file was generated, which made me believe it was no longer necessary. Alas, it wasn't the case. The database was extremely slow after the upgrade chewing up 100% cpu time, and it wasn't until I manually ran vacuumdb" --all --analyze-in-stages that cpu usage dropped. Was there a knob that I missed, or is this just a bug in pg_upgrade 15? Thanks.
pg_upgrade v15 not generating analyze_new_cluster.sh
Hi, all. When pg_upgrading from PG 11 to 13, a file analyze_new_cluster.sh was generated in the current directory which ran vacuumdb --all --analyze-in-stages When upgrading from 13 to 15.3 no such file was generated, which made me believe it was no longer necessary. Alas, it wasn't the case. The database was extremely slow after the upgrade chewing up 100% cpu time, and it wasn't until I manually ran vacuumdb" --all --analyze-in-stages that cpu usage dropped. Was there a knob that I missed, or is this just a bug in pg_upgrade 15? Thanks.
Re: pg_upgrade v15 not generating analyze_new_cluster.sh
On Tue, Jun 13, 2023 at 10:35 AM rihad wrote: > > Hi, all. When pg_upgrading from PG 11 to 13, a file analyze_new_cluster.sh > was generated in the current directory which ran vacuumdb --all > --analyze-in-stages > > When upgrading from 13 to 15.3 no such file was generated, which made me > believe it was no longer necessary. Alas, it wasn't the case. The database > was extremely slow after the upgrade chewing up 100% cpu time, and it wasn't > until I manually ran vacuumdb" --all --analyze-in-stages that cpu usage > dropped. Was there a knob that I missed, or is this just a bug in pg_upgrade > 15? Thanks. If you look at the output of pg_upgrade, it still tells you to run "vacuumdb --all --analyze-in-stages". Since that was the *only* command that was in the script, the script is no longer generated and you're expected to run the command directly instead. It does not remove the need for the job, just instructs you to do it without the script. This change was made in PostgreSQL 14, not 15, and is listed in the release notes there. When you upgrade "past" a version, it's recommended you read the release notes for the intermediate versions as well when looking for changes, as those will not be included in the notes for the newer version. -- Magnus Hagander Me: https://www.hagander.net/ Work: https://www.redpill-linpro.com/
Re: Dynamic binding issue
Because the function is general and should do the same thing for many
different table.
Unique constraint, each table must have a field with a specific type
Il mar 13 giu 2023, 01:31 Adrian Klaver ha
scritto:
> On 6/12/23 15:13, Lorusso Domenico wrote:
> > Hello guys,
> > I'm a problem with dynamic sql.
> > I am trying to write a generic function that is able to read and update
> > a table based on some data coming from e previous record.
> > Here the example
> > _sqlStr=format('select *
> >from %1$s.%2$s
> >where (' || array_to_string(_activeRec.pk_columns_list, ',') || ') in
> > (select ' ||
> >'row($1[''' || array_to_string(_activeRec.pk_columns_list,
> > '''],$1[''') || ''']))'
> >, _activeRec.name_of_schema, _activeRec.main_table);
> >
> > execute _sqlStr using oldRec into _rec;
> >
> > My problem is oldRec is a type record, so the substitution performed by
> > execute fails, because it can't recognize the field if the variable is
> > record and not a specific composite record type.
>
> Why not use a row type?:
>
>
> https://www.postgresql.org/docs/current/plpgsql-declarations.html#PLPGSQL-DECLARATION-ROWTYPES
>
> >
> > I suppose this is a recurrent question, but I can't find a solution...
> >
> > --
> > Domenico L.
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
Re: pg_upgrade v15 not generating analyze_new_cluster.sh
On 6/13/23 12:57, Magnus Hagander wrote: On Tue, Jun 13, 2023 at 10:35 AM rihad wrote: Hi, all. When pg_upgrading from PG 11 to 13, a file analyze_new_cluster.sh was generated in the current directory which ran vacuumdb --all --analyze-in-stages When upgrading from 13 to 15.3 no such file was generated, which made me believe it was no longer necessary. Alas, it wasn't the case. The database was extremely slow after the upgrade chewing up 100% cpu time, and it wasn't until I manually ran vacuumdb" --all --analyze-in-stages that cpu usage dropped. Was there a knob that I missed, or is this just a bug in pg_upgrade 15? Thanks. If you look at the output of pg_upgrade, it still tells you to run "vacuumdb --all --analyze-in-stages". Since that was the *only* command that was in the script, the script is no longer generated and you're expected to run the command directly instead. It does not remove the need for the job, just instructs you to do it without the script. This change was made in PostgreSQL 14, not 15, and is listed in the release notes there. When you upgrade "past" a version, it's recommended you read the release notes for the intermediate versions as well when looking for changes, as those will not be included in the notes for the newer version. Oh, sht... thanks. pg_upgrade was one of several commands my script ran, that also removed PG 13 & installed PG 15 etc right after that, that's why I missed pg_upgrade's output and to be honest wasn't expecting it to say anything important, because, for instance, it did generate update_extensions.sql and delete_old_cluster.sh, which is, btw, also a one-liner)) I'd rather that file wasn't generated but just mentioned in the output as it has nothing to do with how new cluster works, than mentioning vacuumdb as it's crucial to make PG useful in most non-test scenarios at all.
Re: Reproducing incorrect order with order by in a subquery
Ruslan Zakirov schrieb am 13.06.2023 um 09:49: > For example I have a query: > > SELECT main.*, count(*) OVER () FROM (SELECT DISTINCT ... ORDER BY X) > main; > > So the `ORDER BY` clause ended up in a subquery. Most of the time > ordering works until it doesn't. > > Can you help me create a set of test tables with some data to > reproduce this problem more repeatedly? I just want to write a > regression test to make sure it doesn't happen again. Your final/overall query has no ORDER BY, so Postgres is free to return the result in any order it likes. You will have to add an ORDER BY to the "main" part to get a guaranteed sort order
Re: [Beginner Question] Will the backup wal file take too much storage space?
On Tue, 13 Jun 2023 at 10:01, Wen Yi <896634...@qq.com> wrote: > As you can see, base on the wal, we can restore the database to any check > point, but sometimes, If we insert a large column to the postgres (Up to 1 > GB), the postgres will also create a 1 GB wal to store it. You normally restore from a checkpoint and keep the logs from there. Checkpoints are done to avoid the need to keep all the WAL files from the moment you created the database. This is when you want the normal things, to be able to restore to a correct state. Checkpoints indicate all the wal files before them have been applied and can be discarded. Checkpoints are not restore points, you restore to a point in time. With a copy of the data files and all the wals AFTER the copy you can advance the copy to a correct state after that point in time. When you insert postgres record it in the wal, once it is there pg knows it cannot be lost. Then it updates the main data files when it is more convenient. And then it deletes or recycles old wal files. But if you want to restore to ANY point in time you would need a copy of the initial state of the database and ALL the wal files. Normally you do not want to do this, at most you want to restore to "any point in the last 3 days", in which case you will need a copy of the datafiles from 3 days ago plus all the wals from there plus change. > In this situation, will the backup wal file take too much storage space? This is confusing. WALs are not backup per se, they are a recovery mechanism. You do a backup of the main data files and the wal files because you do not want to stop the world to do the backup. The COPY of the datafiles and the wals is the backup. If you want to restore to a very far point in a DB which has very large files it will take a lot of space. But if you did it that way the copy of the data files will not be in the initial ( empty ) copy of the datafiles, it will just be in the wal copy, and it needs to be somewhere if you want to do that. Francisco Olarte.
Re: "paths" between two ROLEs
On 6/13/23 04:17, Dominique Devienne wrote: Hi. We emulated a legacy security model (enforced in C/C++ code) into "layers" of PostgreSQL ROLEs and GRANTs, thus enforced database-side. To troubleshoot and validate that emulation, I'd like to introspect ROLE membership to: 1) Output the ROLE "path(s)" between any two ROLEs. Typically between the LOGIN USER and the ROLE that control access to a particular SCHEMA. In our model, there can be several ways the two end-roles are connected, involving a variable number of roles. So it has to be a recursive query. 2) target-end ROLEs (controlling access to SCHEMAs, again) follow a naming convention, so they can be identified using a LIKE pattern. Output all target ROLEs (aggregating each "paths" to the source-ROLE in an text[]) a given LOGIN USER has access to. I'd appreciate either example SQL for the above; or hints to achieve the above. My CTE "foo" is not great, thus reaching out to the community to avoid wasting too much time on this on my own. This shows the path between roles taken which provides a particular privilege for a particular object: https://github.com/CrunchyData/crunchy_check_access It might do for you as-is, or at least you can use it as an example. HTH, -- Joe Conway PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com
Re: [Beginner Question] Will the backup wal file take too much storage space?
On 6/13/23 06:34, Francisco Olarte wrote: [snip] But if you want to restore to ANY point in time you would need a copy of the initial state of the database and ALL the wal files. Normally you do not want to do this, at most you want to restore to "any point in the last 3 days", in which case you will need a copy of the datafiles from 3 days ago plus all the wals from there plus change. [snip] This is confusing. WALs are not backup per se, they are a recovery mechanism. You do a backup of the main data files and the wal files because you do not want to stop the world to do the backup. The COPY of the datafiles and the wals is the backup. If you want to restore to a very far point in a DB which has very large files it will take a lot of space. But if you did it that way the copy of the data files will not be in the initial ( empty ) copy of the datafiles, it will just be in the wal copy, and it needs to be somewhere if you want to do that. PgBackRest (and presumably barman) handles all this for you. Even compresses the WAL and data files. -- Born in Arizona, moved to Babylonia.
Re: SOC II Type 2 report.
Hi, > On Jun 12, 2023, at 11:57, Raj Kiran wrote: > Prokopto is completing our annual vendor review process. Please share your > most recent SOC II Type 2 report. The PostgreSQL project isn't SOC2 certified, and will almost certainly never be. If you require SOC2 compliance, you'll need to work with a commercial (that is to say, not free) vendor who is SOC2 certified in order to bring PostgreSQL under their certification.
Re: Reproducing incorrect order with order by in a subquery
On Tue, Jun 13, 2023 at 1:26 PM Thomas Kellerer wrote: > Ruslan Zakirov schrieb am 13.06.2023 um 09:49: > > For example I have a query: > > > > SELECT main.*, count(*) OVER () FROM (SELECT DISTINCT ... ORDER BY X) > > main; > > > > So the `ORDER BY` clause ended up in a subquery. Most of the time > > ordering works until it doesn't. > > > > Can you help me create a set of test tables with some data to > > reproduce this problem more repeatedly? I just want to write a > > regression test to make sure it doesn't happen again. > Your final/overall query has no ORDER BY, so Postgres is free to return > the result in any order it likes. > > You will have to add an ORDER BY to the "main" part to get a guaranteed > sort order > I know how to fix the problem and I know that ORDER BY should be in the outermost select. However, I want to write a test case that shows that the old code is wrong, but can not create minimal set of tables to reproduce it. With this I'm looking for help. -- Best regards, Ruslan.
Re: Reproducing incorrect order with order by in a subquery
Ruslan Zakirov writes: > I know how to fix the problem and I know that ORDER BY should be in the > outermost select. > However, I want to write a test case that shows that the old code is wrong, > but can not create > minimal set of tables to reproduce it. With this I'm looking for help. The ORDER BY in the sub-select will be honored at the output of the sub-select. To have a different ordering at the final output, you need the upper query to do something that would re-order the rows. Joining the sub-select to something else might make that happen, or you could apply DISTINCT or some other non-trivial processing in the upper query. regards, tom lane
Re: "paths" between two ROLEs
On Tue, Jun 13, 2023 at 2:20 PM Joe Conway wrote: > On 6/13/23 04:17, Dominique Devienne wrote: > > To troubleshoot and validate that emulation, I'd like to introspect ROLE > > membership to: > > > > 1) Output the ROLE "path(s)" between any two ROLEs.[...] > > 2) Output all target ROLEs [...] a given LOGIN USER has access to. > > This shows the path between roles taken which provides a particular > privilege for a particular object: > > https://github.com/CrunchyData/crunchy_check_access > > It might do for you as-is, or at least you can use it as an example. > Thanks. I'd never seem a nested-for-loop with recursive calls of a function, returning rows. Interesting mix of SQL and procedural / imperative code to generate a view-like output. Not quite what I was looking for though. But thank you for sharing. Very instructional. --DD PS: I'll try to work out the SQL myself then, and if I get stuck, I'll ask a more actionable question with where I'm at so far.
Re: Reproducing incorrect order with order by in a subquery
On Tue, Jun 13, 2023 at 6:06 PM Tom Lane wrote:
> Ruslan Zakirov writes:
> > I know how to fix the problem and I know that ORDER BY should be in the
> > outermost select.
>
> > However, I want to write a test case that shows that the old code is
> wrong,
> > but can not create
> > minimal set of tables to reproduce it. With this I'm looking for help.
>
> The ORDER BY in the sub-select will be honored at the output of the
> sub-select. To have a different ordering at the final output, you
> need the upper query to do something that would re-order the rows.
> Joining the sub-select to something else might make that happen,
> or you could apply DISTINCT or some other non-trivial processing
> in the upper query.
>
> regards, tom lane
>
Hello Tom,
Thanks for replying. Maybe I'm just wrong in my assumption. A user reports
incorrect order in the following query:
SELECT main.*, COUNT(main.id) OVER() AS search_builder_count_all FROM (
SELECT DISTINCT main.* FROM Tickets main
LEFT JOIN Groups Groups_2 ON ( Groups_2.Domain = 'RT::Ticket-Role' ) AND
( Groups_2.Instance = main.id )
JOIN Queues Queues_1 ON ( Queues_1.id = main.Queue )
LEFT JOIN CachedGroupMembers CachedGroupMembers_3 ON (
CachedGroupMembers_3.Disabled = '0' ) AND ( CachedGroupMembers_3.MemberId
IN ('38', '837', ... , '987', '58468') ) AND ( CachedGroupMembers_3.GroupId
= Groups_2.id )
WHERE ( ( main.Queue IN ('1', ... , '20') OR (
CachedGroupMembers_3.MemberId IS NOT NULL AND LOWER(Groups_2.Name) IN
('cc', 'requestor') ) OR ( main.Owner = '38' ) )
AND (main.IsMerged IS NULL)
AND (main.Status != 'deleted')
AND (main.Type = 'ticket')
AND (main.Owner = '6' AND ( ( Queues_1.Lifecycle = 'default' AND
main.Status IN ('new', 'open', 'stalled') ) OR ( Queues_1.Lifecycle =
'approvals' AND main.Status IN ('new', 'open', 'stalled') ) )
) ORDER BY main.Created DESC ) main LIMIT 50
We have an option in our product that makes this query simpler, no joins in
the subquery. The user reports that using this option helps with order.
This is a too complex query to build a test on. Tried simpler scenarios and
failed.
--
Best regards, Ruslan.
Re: Reproducing incorrect order with order by in a subquery
On Tue, Jun 13, 2023 at 10:55 AM Ruslan Zakirov
wrote:
> Thanks for replying. Maybe I'm just wrong in my assumption. A user reports
> incorrect order in the following query:
>
> SELECT main.*, COUNT(main.id) OVER() AS search_builder_count_all FROM (
> SELECT DISTINCT main.* FROM Tickets main
> LEFT JOIN Groups Groups_2 ON ( Groups_2.Domain = 'RT::Ticket-Role' ) AND
> ( Groups_2.Instance = main.id )
> JOIN Queues Queues_1 ON ( Queues_1.id = main.Queue )
> LEFT JOIN CachedGroupMembers CachedGroupMembers_3 ON (
> CachedGroupMembers_3.Disabled = '0' ) AND ( CachedGroupMembers_3.MemberId
> IN ('38', '837', ... , '987', '58468') ) AND ( CachedGroupMembers_3.GroupId
> = Groups_2.id )
> WHERE ( ( main.Queue IN ('1', ... , '20') OR (
> CachedGroupMembers_3.MemberId IS NOT NULL AND LOWER(Groups_2.Name) IN
> ('cc', 'requestor') ) OR ( main.Owner = '38' ) )
>AND (main.IsMerged IS NULL)
>AND (main.Status != 'deleted')
>AND (main.Type = 'ticket')
>AND (main.Owner = '6' AND ( ( Queues_1.Lifecycle = 'default' AND
> main.Status IN ('new', 'open', 'stalled') ) OR ( Queues_1.Lifecycle =
> 'approvals' AND main.Status IN ('new', 'open', 'stalled') ) )
> ) ORDER BY main.Created DESC ) main LIMIT 50
>
> We have an option in our product that makes this query simpler, no joins
> in the subquery. The user reports that using this option helps with order.
>
> This is a too complex query to build a test on. Tried simpler scenarios
> and failed.
>
>
If you want guaranteed ordered output you must place the order by in the
outermost query level (i.e., before your limit 50). Trying to do that for
dynamic SQL where you don't actually know what query you are working with
is going to be a challenge - maybe force the user to have the order by
column first in their query then just say "ORDER BY 1" in the wrapper query
you are adding? Basically have them write "row_number() over (order by)"
for their query and you then order by row number.
David J.
Exclusion constraint with negated operator?
Hi, I wanted to create an exclusion constraint like EXCLUDE (c1 WITH =, c2 with <>) This gives an error: operator <>(integer,integer) is not a member of operator family "integer_ops" I can resolve that by using gist and the btree_gist extension. My question is can I somehow express something like EXCLUDE (c1 WITH =, c2 with NOT =) It seems that's not possible at the moment. But is there any obstacle in principle or is it just not implemented? We have a ton of overlap operators (geometric, ranges, arrays) but none for the opposite. Sometimes that's useful for exclusion constraints. Thanks, Torsten
Helping planner to chose sequential scan when it improves performance
Hi all, I recently started at a new firm and have been trying to help to grok certain planner behavior. A strip-down example of the sort of join we do in the database looks like this, wherein we join two tables that have about 1 million rows: -- VACUUM (FULL, VERBOSE, ANALYZE), run the query twice first, then... EXPLAIN(ANALYZE, VERBOSE, COSTS, SETTINGS, BUFFERS, WAL, TIMING, SUMMARY) SELECT ci.conversation_uuid, ci.item_uuid, ci.tenant_id, it.item_subject, it.item_body_start FROM conversation_item AS ci INNER JOIN item_text AS it ON it.item_uuid = ci.item_uuid; -- The necessary DDL that creates these tables and indexes is attached. I've commented out some extra stuff that isn't directly related to the above query. Depending on config, we get different results in terms of performance (EXPLAIN output attached): PLAN A (default config, effective cache size just shy of 15GB): 3.829 seconds. A nested loop is used to probe the hash index `conversation_item_item_hash_index` for each row of item_text. Although the cost of probing once is low, a fair amount of time passes because the operation is repeated ~1.3 million times. PLAN B (enable_indexscan off, effective cache same as before): 3.254 seconds (~15% speedup, sometimes 30%). Both tables are scanned sequentially and conversation_item is hashed before results are combined with a hash join. PLAN C: (random_page_cost = 8.0, instead of default 4, effective cache same as before): 2.959 (~23% speedup, sometimes 38%). Same overall plan as PLAN B, some differences in buffers and I/O. I'll note we had to get to 8.0 before we saw a change to planner behavior; 5.0, 6.0, and 7.0 were too low to make a difference. Environment: Postgres 15.2 Amazon RDS — db.m6g.2xlarge Questions: 1. In Plan A, what factors are causing the planner to select a substantially slower plan despite having recent stats about number of rows? 2. Is there a substantial difference between the on-the-fly hash done in Plan B and Plan C compared to the hash-index used in Plan A? Can I assume they are essentially the same? Perhaps there are there differences in how they're applied? 3. Is it common to see values for random_page_cost set as high as 8.0? We would of course need to investigate whether we see a net positive or net negative impact on other queries, to adopt this as a general setting, but is it a proposal we should actually consider? 4. Maybe we are barking up the wrong tree with the previous questions. Are there other configuration parameters we should consider first to improve performance in situations like the one illustrated? 5. Are there other problems with our schema, query, or plans shown here? Other approaches (or tools/analyses) we should consider? Hash Join (cost=53933.69..255802.87 rows=1395542 width=216) (actual time=422.466..2868.948 rows=1394633 loops=1)" Output: ci.conversation_uuid, ci.item_uuid, ci.tenant_id, it.item_subject, it.item_body_start" Inner Unique: true" Hash Cond: (it.item_uuid = ci.item_uuid)" Buffers: shared hit=12283 read=95974 dirtied=77, temp read=44113 written=44113" I/O Timings: shared/local read=462.195, temp read=86.413 write=423.363" WAL: records=1 bytes=58" -> Seq Scan on item_text it (cost=0.00..110641.18 rows=1401518 width=196) (actual time=0.012..854.842 rows=1394633 loops=1)" Output: it.item_subject, it.item_body_start, it.item_uuid" Buffers: shared hit=652 read=95974 dirtied=77" I/O Timings: shared/local read=462.195" WAL: records=1 bytes=58" -> Hash (cost=25586.42..25586.42 rows=1395542 width=36) (actual time=422.386..422.387 rows=1394633 loops=1)" Output: ci.conversation_uuid, ci.item_uuid, ci.tenant_id" Buckets: 131072 Batches: 16 Memory Usage: 6799kB" Buffers: shared hit=11631, temp written=8932" I/O Timings: temp write=78.785" -> Seq Scan on conversation_item ci (cost=0.00..25586.42 rows=1395542 width=36) (actual time=0.004..133.304 rows=1394633 loops=1)" Output: ci.conversation_uuid, ci.item_uuid, ci.tenant_id" Buffers: shared hit=11631" Settings: effective_cache_size = '16053152kB', jit = 'off', random_page_cost = '8'" Query Identifier: 5005100605804837348" Planning:" Buffers: shared hit=398 read=7" I/O Timings: shared/local read=1.400" Planning Time: 2.379 ms" Execution Time: 2956.264 ms" Generated with: EXPLAIN(ANALYZE, VERBOSE, COSTS, SETTINGS, BUFFERS, WAL, TIMING, SUMMARY) SELECT ci.conversation_uuid, ci.item_uuid, ci.tenant_id, it.item_subject, it.item_body_start FROM conversation_item AS ci INNER JOIN item_text AS it ON it.item_uuid = ci.item_uuid; Hash Join (cost=53126.96..251632.49 rows=1374665 width=216) (actual time=475.472..3162.922 rows=1374687 loops=1)" Output: ci.conversation_u
Re: Exclusion constraint with negated operator?
=?UTF-8?Q?Torsten_F=C3=B6rtsch?= writes: > My question is can I somehow express something like > EXCLUDE (c1 WITH =, c2 with NOT =) > It seems that's not possible at the moment. But is there any obstacle in > principle or is it just not implemented? Well, it'd likely be a bad idea. Indexes are meant to help you quickly find a small part of a table that satisfies a condition. Finding the probably-much-larger part of the table that doesn't satisfy the condition is something they are bad at. This is why "=" is an indexable operator while "<>" is not. It's not impossible in principle for "<>" to be an index operator, but the set of cases where indexing on such a condition would beat a seqscan is likely to be uselessly small. By the same token, EXCLUDE constraints using such a condition would be unpleasantly inefficient. regards, tom lane
