Msg 8623, Level 16, State 1, Line 288 The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.
@JoeObbish Not following. Say you delete from the parent table (with the unique key) the check on the child table(s) will generally not have a unique constraint
@sp_BlitzErik Funny story. My first time to Kuala Lumpur I stayed at the Armada Hotel. In their booklet they had a list of things to do. One was to visit the night markets (pasar-pasar malam). The brochure said advertised it as "a hotbed of triad activity" where "you can find all kinds of contraband."
@EvanCarroll It's actually interesting. When I got the job at Adjust I had to pass a C coding challenge which was a bit of a challenge because I had not touched C in 15 years basically since I taught myself it and basically wrote Perl in C. Since I started I have worked with good C programmers and upped my C game a lot.
One of the people I work with here is involved in writing operating systems and tends to advocate using the stack for as much memory management as possible.
But knowing how palloc works and memory contexts is a very nice thing and I totally agree with using the same basic approach for clients too.
We need a book on PostgreSQL internals, I've read the Tom Lane ebook but it's wayyy out of date.
I think it covers the v0 function calls.
I think in another year I'll be a lot better at it. Rust and modern C are two things I've got on my todo list, and next years tech-new years resolution is to dive into Pg internals.
@ChrisTravers I'm down if you've got a use-case. =) I'm just unsure of my abilities right now with your work load or whether or not I'm the best guy for the job. I do full stack web dev, rest apis, and work with open source scripting languages, angular and the like. But I've done pretty much everything from arduino on up. I also work with functional reactive programming with rx.js which seems a lot like the Apache project you linked yesterday.
@EvanCarroll The problem is that most of the right people for the job work for about 3 companies (2Q, EDB, Citus, and maybe a few others). I don't expect to find a perfect match. Getting people who can jump in and become the right person.... that's probably where it is at.
@PaulWhite A couple of recent messages seemed to belong there much more than here and I didn't feel like moving them to any other trash room. Now I'm not sure I'm as concerned about them as I was in the morning. Sorry. If there's still any argument for unfreezing it, I guess for me it would now be "to keep it handy".
I have a large table that get populated from a view. This is done because the view takes a long time to run and it is easier to have the data readily available in a table. A procedure is run every so often that updates the table.
TRUNCATE TABLE LargeTable
INSERT INTO LargeTable
SELECT *
FR...
just wanting to see what people think of this here.
So I have a situation where I have to do some bulk writes directly from one Pg db into another and the foreign data wrapper is not going to work since it writes one line at a time.
so the current approach is to implement a copy bulk write interface.
and two phase commit.
the issue with two phase commit is the cleanup if we don't get immediately to a globally consistent spot
So my thinking is to fire up a background worker to handle the cleanup.
(which can the happen after the transaction locally completes or rolls back)
anyone have any ideas as to this being a bad idea?
Breaking it down 1. Export source to staging 2. Staging to target 3. Update as processed on the source
If step 2 fails, step 3 does not run -> next batch just tries again with missed data and new data
If step 3 fails, data gets reloaded but anything already there is ignored
You may be able to have step 2 and step 3 in one transaction (possible in SQL Server)
I'd also use a Session identifier for each run, tracking in a separate table, and an extra column in the staging table This would be used for an later cleanup of staging.
Basically what I did a few years ago, where source was a risk calculation engine. Of course no need to write back to it.
tl;dr don't do it transactionally; do it in stages and with idempotence
On a data warehouse: i. Put foreign key relationships between the fact and dim tables. You can disable them during a load for performance if necessary. ii. Most DW queries are table scans of the fact table anyway. Unless you're only bringing back a small fraction of the rows (few % or less) it is more efficient to do a table scan. iii. It's worth putting indexes on fact tables if you have a significant amount of query traffic that is selective enough for the indexes to be worthwhile. By and large, the optimiser will just ignore indexes that aren't selective enough anyway.
@ChrisTravers Can you do 2PC natively on PG? (i.e. does it support XA?)
@ChrisTravers Normally you would use a TP monitor for that, although that might get you into large sums of money. Never seen an open-source one that was discrete from a bean container.
@ConcernedOfTunbridgeWells Ok so XA in PostgreSQL is such that once you ask if a transaction can be committed it becomes immutable and guaranteed commutable. I.e. if someone trips over a power cord when the server comes up you can still commit the transaction.
Are there any good way to dive into a database you don't know the relations of? My first thought was to put it into something like Visual Studio and get it to map the relationships visually.
@TomV database is exactly the same just that the diagram was made on sqlexpress as we didnt have privelages (3rd party hosting) now we have our own server so we have the privelages to make the diagrams so i need to put them on new db but making them again is tedious
@ConcernedOfTunbridgeWells I actually don't think it is so bad. Basically you need a guaranteed log. You need enough info in it to make appropriate decisions and you need a cleanup process that can run through the log and make appropriate decisions at the end.
Not sure if it fits into your specific work load or not. But it sounds like if you're not looking to use master-master replication then likely the same code the wrote to the run the plugin overlaps with your task
@ChrisTravers it seems what they do for BDR is run the same version of the code, and then send the datums as binary from one server to another without parsing them at all.
@JoeObbish it's funny -- i'm thinking through and it i'm coming back to the blog post where you figured out which rows were gonna end up on which thread
I have a large table that get populated from a view. This is done because the view takes a long time to run and it is easier to have the data readily available in a table. A procedure is run every so often that updates the table.
TRUNCATE TABLE LargeTable
INSERT INTO LargeTable
SELECT *
FR...
